October 9, 2018 at 7:58 am
At the moment I have some time on my hands.
So I want to extend a Generation procedure with some 'extra' features.
The following :exec sp_column_list 'ALIAS', 'AA|BB|CC'
exec sp_column_list 'JOIN', 'A123|B456|C789'
Generates a statement where all fields get unique names and the tables are joined.
The JOINS are realised as CROSS JOINS, so no ON clause is needed or used, this is left to the user, who can use this as a starting point.
(Using the generated text (ctrl A) and the Query Designer (ctrl shift Q), the CROSS JOIN can be altered in a JOIN with an ON clause).
Now I want to extend this procedure to generate the ON clauses based on the available constraints and then it is left to the user to ommit or add to this. Also I am thinking about generation this within a WITH construction, so that the user of the stored procedure can easely use this a a starting point.
Is something like this allready available (in SSMS) as a stored procedure or otherwise ?
Suggestions adding to this idea or otherwise are welcome. (???)
Thanks for your time and attention.
Ben
October 9, 2018 at 9:19 am
What is "sp_column_list"? It's not an MS sp, which suggests it's a user SP: Is the sp_ prefix still a no-no?
I'm not entirely sure what your goal is here. Could you try to elaborate? For a specific execution of your SP (which I suggest changing the name of), what are you expecting as the output? If you're wanting us to help you change the existing SP, we're (probably) going to need to current definition too.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 9, 2018 at 10:00 am
Thom A - Tuesday, October 9, 2018 9:19 AMWhat is "sp_column_list"? It's not an MS sp, which suggests it's a user SP: Is the sp_ prefix still a no-no?I'm not entirely sure what your goal is here. Could you try to elaborate? For a specific execution of your SP (which I suggest changing the name of), what are you expecting as the output? If you're wanting us to help you change the existing SP, we're (probably) going to need to current definition too.
SP_Column_list,
is my stored procedure (not an MS procedure), it started of with the generation of columnlists for just a comma seperated list, and an equal list for compares, an update list to perform updates. And was later on extended with an extention to generate a join over several tables, where field names where kept unique.
Now I want to have some more features into this Stored procedure. I want a generated ON clause, where the clause is based on the constraints of the tables. If there is a constraint, the JOIN should be on that constraint. The code should work and should provide a starting point, so that a SSMS user can just type in some aliasses and some table names and get working code (not neccesarely functional), so that with some (minor) alterations the code does what the user intended it to do.
I'll try to provide an example of the workings. (Input and output of the stored procedure). The whole procedure consists of a number of procedures and it is not very practical to show them here.
And why the name change for the procedure? (The name reflects the purpose the procedure was designed for at the start, now it does a bit more 🙂 ).
Ben
October 9, 2018 at 10:12 am
ben.brugman - Tuesday, October 9, 2018 10:00 AMThom A - Tuesday, October 9, 2018 9:19 AMWhat is "sp_column_list"? It's not an MS sp, which suggests it's a user SP: Is the sp_ prefix still a no-no?And why the name change for the procedure? (The name reflects the purpose the procedure was designed for at the start, now it does a bit more 🙂 ).
For the reasons listed in the link. The article goes into it further, however, "sp_" is reserved by Microsoft; even they recommend against the use of the sp_ prefix: https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/stored-procedures-database-engine?view=sql-server-2017#types-of-stored-procedures
System procedures are included with SQL Server. They are physically stored in the internal, hidden Resource database and logically appear in the sys schema of every system- and user-defined database. In addition, the msdb database also contains system stored procedures in the dbo schema that are used for scheduling alerts and jobs. Because system procedures start with the prefix sp_, we recommend that you do not use this prefix when naming user-defined procedures. For a complete list of system procedures, see
Underlining my own for emphasis.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 11, 2018 at 12:52 am
Thom A - Tuesday, October 9, 2018 10:12 AMFor the reasons listed in the link. The article goes into it further, however, "sp_" is reserved by Microsoft; even they recommend against the use of the sp_ prefix: https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/stored-procedures-database-engine?view=sql-server-2017#types-of-stored-proceduresSystem procedures are included with SQL Server. They are physically stored in the internal, hidden Resource database and logically appear in the sys schema of every system- and user-defined database. In addition, the msdb database also contains system stored procedures in the dbo schema that are used for scheduling alerts and jobs. Because system procedures start with the prefix sp_, we recommend that you do not use this prefix when naming user-defined procedures. For a complete list of system procedures, see
Underlining my own for emphasis.
Thanks Thom,
First of all thanks for your response. In general I do take the advise given within this group. A lot of advises given by MS especially around 15 years ago where often of the mark, so in general I am critical to the advises of MS.
Second, sorry for the late response, I had a day off.
To SP or not to SP that is the question.
For Stored Procedures to be used in all databases on an instance the stored procedure should be located in the Master, to make them calleble their name should start with SP_, otherwise they are not calleble from other databases.
Then I use a
EXEC sp_ms_marksystemobject 'SP_<name>'
To let them work as if running in the database where the call is initiated.
I want the stored procedure to be 'generic' and also be able to call the procedure in databases where I do not want to create anything (including stored procedures). So hence the name starting with SP_.
Thanks for your time and advise.
Original request is still open and advise and suggestions are welcome.
Ben
October 11, 2018 at 2:12 am
ben.brugman - Thursday, October 11, 2018 12:52 AMThom A - Tuesday, October 9, 2018 10:12 AMFor the reasons listed in the link. The article goes into it further, however, "sp_" is reserved by Microsoft; even they recommend against the use of the sp_ prefix: https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/stored-procedures-database-engine?view=sql-server-2017#types-of-stored-proceduresSystem procedures are included with SQL Server. They are physically stored in the internal, hidden Resource database and logically appear in the sys schema of every system- and user-defined database. In addition, the msdb database also contains system stored procedures in the dbo schema that are used for scheduling alerts and jobs. Because system procedures start with the prefix sp_, we recommend that you do not use this prefix when naming user-defined procedures. For a complete list of system procedures, see
Underlining my own for emphasis.
Thanks Thom,
First of all thanks for your response. In general I do take the advise given within this group. A lot of advises given by MS especially around 15 years ago where often of the mark, so in general I am critical to the advises of MS.
Second, sorry for the late response, I had a day off.To SP or not to SP that is the question.
For Stored Procedures to be used in all databases on an instance the stored procedure should be located in the Master, to make them calleble their name should start with SP_, otherwise they are not calleble from other databases.
Then I use a
EXEC sp_ms_marksystemobject 'SP_<name>'
To let them work as if running in the database where the call is initiated.I want the stored procedure to be 'generic' and also be able to call the procedure in databases where I do not want to create anything (including stored procedures). So hence the name starting with SP_.
Thanks for your time and advise.
Original request is still open and advise and suggestions are welcome.
Ben
So, what you're saying is, you're actually going to set it up as a system SP? Ok, then yes, that works. There is guidance out there on that, but (like always), on doing this type of thing though, and some caveats apply. if, however, it won't be set up as a system sp, then i still recommend dropping the "sp_".
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 11, 2018 at 3:37 am
Thom A - Thursday, October 11, 2018 2:12 AMSo, what you're saying is, you're actually going to set it up as a system SP? Ok, then yes, that works. There is guidance out there on that, but (like always), on doing this type of thing though, and some caveats apply. if, however, it won't be set up as a system sp, then i still recommend dropping the "sp_".
Yes.
And I do agree.
Ben
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply