Stored Procedure Generator script

[cmwright]

Hi all,

I recently decided to take a swipe at writing a script to generate stored procedures. I searched the web, and there are programs that do this for you, but none of them had the options I wanted. I wanted a generator that would write a procedure, complete with comments of my making at the top, and leave very little cleanup for me to do. I have 5 of these, but I'll just post the first 2. If you want me to post the rest, just let me know. The update one isn't perfect, yet. I need to put in the size for decimal data types. Bonus points if you beat me to it!

I'd also love for anyone who is interested to clean up/add to these. Group effort.

INSERT

set nocount on

declare @TableName varchar(200)
, @SQLScript varchar(8000)
, @Comments varchar(8000)
, @Variables varchar(8000)
, @Columns varchar(8000)
, @Values varchar(8000)

set @TableName = 'Inventory_PlannedShipments_LNK'
set @Comments = ''
set @Variables = ''
set @Columns = ''
set @Values = ''

select @Variables = @Variables + char(9) + ',' + char(9) + '@' + COLUMN_NAME + char(9) + char(9) + DATA_TYPE + left((isnull('(' + convert(varchar(4), CHARACTER_MAXIMUM_LENGTH) + ')', '')), len((isnull(convert(varchar(4), CHARACTER_MAXIMUM_LENGTH), ''))) + 2) + char(10),
@Comments = @Comments + char(9) + char(9) + char(9) + '@' + COLUMN_NAME + char(9) + '::' + char(9) + 'description' + char(10),
@Columns = @Columns + char(9) + COLUMN_NAME + char(10) + char(9) + ',',
@Values = @Values + char(9) + ',' + char(9) + '@' + COLUMN_NAME + char(10)
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @TableName

set @Variables = char(9) + char(9) + Right(@Variables, len(@Variables) - 3)
set @Comments = Right(@Comments, len(@Comments) - 2)
set @Columns = char(9) + Left(@Columns, len(@Columns) - 2)
set @Values = char(9) + char(9) + Right(@Values, len(@Values) - 3)

set @SQLScript = '/*
Procedure name :: p_' + replace(replace(@TableName, '_', ''), 'LNK', '') + '_Insert
Procedure purpose :: Inserts values into the ' + @TableName + ' Table.

Input arguments ::' + @Comments + '
Created :: ' + convert(varchar(20), getdate(), 101) + ' :: ' + right(SYSTEM_USER,len(SYSTEM_USER)-charindex('\',SYSTEM_USER)) + '

*/

create procedure dbo.p_' + replace(replace(@TableName, '_', ''), 'LNK', '') + '_Insert

('
+ @Variables + ')' + char(10) + char(10) + 'as' + char(10) + char(10) + 'set nocount on' + char(10) + char(10) + 'insert into ' + char(9) + @TableName + char(10) + char(9) + '(' + char(10)

+ @Columns + char(9) + ')' + char(10) + 'values' + char(10) + char(9) + '(' + char(10)

+ @Values + char(9) + ')' + char(10) + char(10) + 'set nocount off'

select @SQLScript
set nocount off


UPDATE

set nocount on

declare @TableName varchar(200)
, @SQLScript varchar(8000)
, @Comments varchar(8000)
, @Variables varchar(8000)
, @SetStatements varchar(8000)
, @Where varchar(8000)

set @TableName = 'Inventory_PlannedShipments_LNK'
set @Comments = ''
set @Variables = ''
set @SetStatements = ''
set @Where = ''

select @Variables = @Variables + char(9) + ',' + char(9) + '@' + COLUMN_NAME + char(9) + char(9) + DATA_TYPE + left((isnull('(' + convert(varchar(4), CHARACTER_MAXIMUM_LENGTH) + ')', '')), len((isnull(convert(varchar(4), CHARACTER_MAXIMUM_LENGTH), ''))) + 2) + char(10),
@Comments = @Comments + char(9) + char(9) + char(9) + '@' + COLUMN_NAME + char(9) + '::' + char(9) + 'description' + char(10)
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @TableName

select @SetStatements = @SetStatements + char(9) + c.COLUMN_NAME + char(9) + '= @' + c.COLUMN_NAME + char(10) + char(9) + ','
from INFORMATION_SCHEMA.COLUMNS c
where c.TABLE_NAME = @TableName
AND not exists
( Select 1
From INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS t
ON t.CONSTRAINT_NAME = k.CONSTRAINT_NAME
AND t.CONSTRAINT_TYPE = 'PRIMARY KEY'
Where k.TABLE_NAME = c.TABLE_NAME
and k.COLUMN_NAME = c.COLUMN_NAME
)

select @Where = @Where + char(9) + c.COLUMN_NAME + char(9) + '= @' + c.COLUMN_NAME + char(10) + char(9) + 'and'
from INFORMATION_SCHEMA.COLUMNS c
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
ON k.TABLE_NAME = c.TABLE_NAME
and k.COLUMN_NAME = c.COLUMN_NAME
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS t
ON t.CONSTRAINT_NAME = k.CONSTRAINT_NAME
where c.TABLE_NAME = @TableName
AND t.CONSTRAINT_TYPE = 'PRIMARY KEY'

set @Variables = char(9) + char(9) + Right(@Variables, len(@Variables) - 3)
set @Comments = Right(@Comments, len(@Comments) - 2)
set @SetStatements = char(9) + Left(@SetStatements, len(@SetStatements) - 2)
set @Where = Left(@Where, len(@Where) - 3)

set @SQLScript = '/*
Procedure name :: dbo.p_' + replace(replace(@TableName, '_', ''), 'LNK', '') + '_Update
Procedure purpose :: Updates values for the ' + @TableName + ' Table.

Input arguments ::' + @Comments + '
Created :: ' + convert(varchar(20), getdate(), 101) + ' :: ' + right(SYSTEM_USER,len(SYSTEM_USER)-charindex('\',SYSTEM_USER)) + '

*/

create procedure dbo.p_' + replace(replace(@TableName, '_', ''), 'LNK', '') + '_Update

('
+ @Variables + ')' + char(10) + char(10) + 'as' + char(10) + char(10) + 'set nocount on' + char(10) + char(10)

+ 'update' + char(9) + char(9) + @TableName + char(10) + 'set'

+ @SetStatements

+ 'where' + char(9) + @Where + char(10) + char(10) + 'set nocount off'

select @SQLScript
set nocount off

 

[vongrunt]

What about sp_helptext?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[vongrunt]

Ah... scrap that. Friday afternoon.

This is basically a code generator for INSERT/UPDATE procedures?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[cmwright]

Hah. Right. sp_helptext isn't that helpful here.

Yeah. It's code to generate INSERT/UPDATE procedures. I have one for delete, get (1 record based on key value), and list, too.

Like I said, though, additions and corrections welcome.

-Chris