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

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 generate 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’