Rob Kraft's Software Development Blog

Software Development Insights

Use a SQL script to generate well formatted stored procedures in SQL Server

Posted by robkraft on February 9, 2012

Occasionally you realize that you desire to create a lot of stored procedures, and that the information to build each stored procedure is contained within the database.  But how can you best generate the code from the data?  Well, assuming you like SQL, you can probably use SQL to generated your SQL.

Here is an example of doing just that, along with a few tips to make the code you generate human-readable.

  • Use char(13) + char(10) to wrap your output to the next line.
  • Use char(9) to indent by one tab.
  • Create one line of output for each line of your code-generating SQL script.
  • First write the query that obtains the values you need for code generation without generating the code to make sure you have the correct result set.
  • Generate your output to text, then copy the output into a new query window and it should look great!

This example creates a stored procedure for each table in your database.  Each stored procedure will query the first integer column in the table and return the first row with an integer value greater than the value you passed in.  This particular script will probably not be useful to you, and you may not like the way I formatted my output, but my only intent is to provide you a nice starting point for doing something like this to meet your own needs.  This should work on most versions of SQL Server.  Enjoy!

select
'CREATE PROCEDURE mysp_' + i.TABLE_NAME + 'FIRSTCOL ' + char(13) + char(10)
+ '(' + char(13) + char(10)
+ char(9) + '@' + i.COLUMN_NAME + ' int ' + char(13) + char(10)
+ ')' + char(13) + char(10)
+ 'AS' + char(13) + char(10)
+ 'BEGIN' + char(13) + char(10)
+ char(9) + 'SET NOCOUNT ON' + char(13) + char(10)
+ char(9) + 'DECLARE @Err int' + char(13) + char(10)
+ '/*Comment-Begin*/' + char(13) + char(10)
+ 'SELECT ' + i.COLUMN_NAME + char(13) + char(10)
+ char(9) + 'FROM ' + char(13) + char(10)
+ char(9) + char(9) + i.TABLE_NAME + char(13) + char(10)
+ ' WHERE ' + char(13) + char(10)
+ char(9) + char(9) + i.COLUMN_NAME + ' > @' + i.COLUMN_NAME + char(13) + char(10)
+ '/*Comment-End*/' + char(13) + char(10)
+ char(9) + 'SET @Err = @@Error' + char(13) + char(10)
+ char(9) + 'RETURN @Err' + char(13) + char(10)
+ 'END' + char(13) + char(10)
+ 'GO' + char(13) + char(10)
+ char(13) + char(10)
+ 'GRANT EXEC ON ' + 'mysp_' + i.TABLE_NAME + 'FIRSTCOL ' + 'TO everyone' + char(13) + char(10)
+ 'GO' + char(13) + char(10)
+ char(13) + char(10)
+ char(13) + char(10)
from INFORMATION_SCHEMA.COLUMNS i WHERE i.ORDINAL_POSITION=1 and DATA_TYPE = 'int'
Advertisements

4 Responses to “Use a SQL script to generate well formatted stored procedures in SQL Server”

  1. Dinesh kumar karn said

    its great

  2. your post is very interesting. I like it very much. It is very helpful and useful for me. Thanks for share this valuable post.

  3. Praful said

    sir i don’t Know what is the work of this procedure and how to work it . please explain for me.

    • robkraft said

      This script is an example of using SQL to generate SQL. So if you need to run some SQL against each table in your database, you could use a script like this to generate the SQL you need to run, then you run that SQL. So
      step 1) Write the SQL generation SQL like above
      step 2) Run the SQL you wrote (like the SQL above)
      step 3) Take the results of the script ran in step 2, and run those results as a new script to get your results.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: