Auto generate sp's for any table * usp_create_sps
If you've ever been stuck having to write stored procedures to support an application, check this script out. This script will automatically generate seperate parameter driven select, insert, delete, and update stored procedures. Simply call these stored procedures from your application, passing parameters. This stored procedure requires only one parameter; table name. Creates scripts with names 'usp_insert_tablename', 'usp_delete_tablename' etc. I welcome any comments and suggestions, as I will be improving this constantly.
if exists (select * from sysobjects where id = object_id('usp_create_sps'))
begin
drop procedure usp_create_sps
end
go
create procedure usp_create_sps (
@tablename varchar(50))
as
declare @dropproc varchar(255)
if exists (select * from sysobjects where id = object_id('usp_insert_' + @tablename))
begin
select @dropproc = 'drop procedure usp_insert_' + @tablename
exec (@dropproc)
end
if exists (select * from sysobjects where id = object_id('usp_update_' + @tablename))
begin
select @dropproc = 'drop procedure usp_update_' + @tablename
exec (@dropproc)
end
if exists (select * from sysobjects where id = object_id('usp_delete_' + @tablename))
begin
select @dropproc = 'drop procedure usp_delete_' + @tablename
exec (@dropproc)
end
if exists (select * from sysobjects where id = object_id('usp_select_' + @tablename))
begin
select @dropproc = 'drop procedure usp_select_' + @tablename
exec (@dropproc)
end
-- begin support for parm_listing
declare @plcolumn_name varchar(128)
declare @plisnullablechar(3)
declare @pldata_typevarchar(20)
declare @plcharacter_maximum_length int
declare @plparmvarchar(2000)
-- end support for parm_listing
-- begin support for column_listing
declare@clcolumn_namevarchar(128)
declare@clisnullablechar(3)
declare@clcolumnsvarchar(2000)
-- end supoprt for column_listing
-- begin support for value_listing
declare@vlcolumn_namevarchar(128)
declare@vlisnullablechar(3)
declare@vlcolumnsvarchar(2000)
-- end supoprt for value_listing
-- begin support for set_listing
declare@slcolumn_namevarchar(128)
declare@slisnullablechar(3)
declare @slcolumnsvarchar(2000)
-- end support for set_listing
-- begin support for key_listing
declare @klcolumn_namevarchar(128)
declare@klisnullablevarchar(3)
declare @klcolumnsvarchar(2000)
-- end support for key_listing
-- support for create procedures
declare@insert varchar(8000)
declare @update varchar(8000)
declare @select varchar(8000)
declare @delete varchar(8000)
-- end support for create procedures
parm_listing:
/*---------------------------------------------------------------------------*//* parm_listing begin *//*---------------------------------------------------------------------------*/set@plparm = ''
declare parm_listing cursor
forselect column_name,
is_nullable,
data_type,
character_maximum_length
from information_schema.columns
wheretable_name = @tablename
order by ordinal_position
openparm_listing
fetch next
from parm_listing
into@plcolumn_name,
@plisnullable,
@pldata_type,
@plcharacter_maximum_length
while(@@fetch_status <> -1)
BEGIN
if (@@fetch_status <> -2)
BEGIN
select@plparm =@plparm +
char(64) +
@plcolumn_name +
space(1) +
@pldata_type +
case
when@plcharacter_maximum_length is nullthenspace(1)
when@plcharacter_maximum_length is not null and @plcharacter_maximum_length > 8000 then space(1)
elsechar(40) + convert(varchar(8),@plcharacter_maximum_length) + char(41)
end +
char(10)
END
fetch next
from parm_listing
into@plcolumn_name,
@plisnullable,
@pldata_type,
@plcharacter_maximum_length
select@plparm = @plparm + ','
END
select@plparm = substring(@plparm, 1, len(@plparm)-1) --strip off last comma
closeparm_listing
deallocateparm_listing
/*---------------------------------------------------------------------------*//* parm_listing end *//*---------------------------------------------------------------------------*/
column_listing:
/*---------------------------------------------------------------------------*//* column_listing begin *//*---------------------------------------------------------------------------*/set@clcolumns = ''
declare column_listing cursor
forselect column_name,
is_nullable
from information_schema.columns
wheretable_name = @tablename
order by ordinal_position
opencolumn_listing
fetch next
from column_listing
into@clcolumn_name,
@clisnullable
while(@@fetch_status <> -1)
BEGIN
if (@@fetch_status <> -2)
BEGIN
select@clcolumns =@clcolumns +
@clcolumn_name +
char(44) +
char(10)
END
fetch next
from column_listing
into@clcolumn_name,
@clisnullable
END
select@clcolumns = substring(@clcolumns, 1, len(@clcolumns)-2) --strip off last comma
closecolumn_listing
deallocatecolumn_listing
/*---------------------------------------------------------------------------*//* column_listing end *//*---------------------------------------------------------------------------*/
value_listing:
/*---------------------------------------------------------------------------*//* value_listing begin *//*---------------------------------------------------------------------------*/set@vlcolumns = ''
declare value_listing cursor
forselect column_name,
is_nullable
from information_schema.columns
wheretable_name = @tablename
order by ordinal_position
openvalue_listing
fetch next
from value_listing
into@vlcolumn_name,
@vlisnullable
while(@@fetch_status <> -1)
BEGIN
if (@@fetch_status <> -2)
BEGIN
select@vlcolumns =@vlcolumns +
char(64) +
@vlcolumn_name +
char(44) +
char(10)
END
fetch next
from value_listing
into@vlcolumn_name,
@vlisnullable
END
select@vlcolumns = substring(@vlcolumns, 1, len(@vlcolumns)-2) --strip off last comma
closevalue_listing
deallocatevalue_listing
/*---------------------------------------------------------------------------*//* value_listing end *//*---------------------------------------------------------------------------*/
key_listing:
/*---------------------------------------------------------------------------*//* key_listing begin *//*---------------------------------------------------------------------------*/set@klcolumns = ''
declare key_listing cursor
forselectkeys.column_name
frominformation_schema.columns as cols
joininformation_schema.key_column_usage as keys
oncols.table_name = keys.table_name
andcols.column_name = keys.column_name
joininformation_schema.table_constraints as consts
onkeys.constraint_name = consts.constraint_name
andconsts.constraint_type = 'primary key'
wherekeys.table_name = @tablename
openkey_listing
fetch next
from key_listing
into@klcolumn_name
while(@@fetch_status <> -1)
BEGIN
if (@@fetch_status <> -2)
BEGIN
select@klcolumns =@klcolumns +
@klcolumn_name +
char(61) +
char(64) +
@klcolumn_name +
space (1) +
'and' +
space(1) +
char(10)
END
fetch next
from key_listing
into@klcolumn_name
END
select@klcolumns = substring(@klcolumns, 1, len(@klcolumns)-5) --strip off last 'and'
closekey_listing
deallocatekey_listing
/*---------------------------------------------------------------------------*//* key_listing end *//*---------------------------------------------------------------------------*/
set_listing:
/*---------------------------------------------------------------------------*//* set_listing begin *//*---------------------------------------------------------------------------*/set@slcolumns = ''
declare set_listing cursor
forselect column_name,
is_nullable
from information_schema.columns
wheretable_name = @tablename
order by ordinal_position
openset_listing
fetch next
from set_listing
into@slcolumn_name,
@slisnullable
set@slcolumns = 'set '
while(@@fetch_status <> -1)
BEGIN
if (@@fetch_status <> -2)
BEGIN
select@slcolumns =@slcolumns +
space(1) +
@slcolumn_name +
char(61) +
char(64) +
@slcolumn_name +
char(44) +
char(10)
END
fetch next
from set_listing
into@slcolumn_name,
@slisnullable
END
select@slcolumns = substring(@slcolumns, 1, len(@slcolumns)-2) --strip off last comma
closeset_listing
deallocateset_listing
/*---------------------------------------------------------------------------*//* set_listing end *//*---------------------------------------------------------------------------*/
/*---------------------------------------------------------------------------*//* insert begin *//*---------------------------------------------------------------------------*/select@insert =
'create procedure usp_insert_'
+@tablename
+space(1)
+char(40)
+char(10)
+@plparm
+char(41)
+space(1)
+'as'
+char(10)
+'insert'
+char(10)
+@tablename
+space(1)
+char(40)
+ char(10)
+ @clcolumns
+ char(10)
+ char(41)
+char(10)
+'values'
+space(1)
+ char(40)
+char(10)
+@vlcolumns
+char(10)
+char(41)
exec (@insert)
print @insert
/*---------------------------------------------------------------------------*//* insert end *//*---------------------------------------------------------------------------*/
/*---------------------------------------------------------------------------*//* update begin *//*---------------------------------------------------------------------------*/select@update =
'create procedure usp_update_'
+@tablename
+space(1)
+char(40)
+char(10)
+@plparm
+char(41)
+space(1)
+'as'
+char(10)
+'update'
+char(10)
+@tablename
+space(1)
+ char(10)
+@slcolumns
+space(1)
+char(10)
+'where'
+space(1)
+char(10)
+@klcolumns
+ char(10)
exec (@update)
print @update
/*---------------------------------------------------------------------------*//* update end *//*---------------------------------------------------------------------------*/
/*---------------------------------------------------------------------------*//* delete begin *//*---------------------------------------------------------------------------*/select@delete =
'create procedure usp_delete_'
+@tablename
+space(1)
+char(40)
+char(10)
+@plparm
+char(41)
+space(1)
+'as'
+char(10)
+'delete'
+char(10)
+@tablename
+space(1)
+char(10)
+'where'
+space(1)
+char(10)
+@klcolumns
+ char(10)
exec (@delete)
print @delete
/*---------------------------------------------------------------------------*//* delete end *//*---------------------------------------------------------------------------*/
/*---------------------------------------------------------------------------*//* select begin *//*---------------------------------------------------------------------------*/select@select =
'create procedure usp_select_'
+@tablename
+space(1)
+char(40)
+char(10)
+@plparm
+char(41)
+space(1)
+'as'
+char(10)
+'select'
+char(10)
+@clcolumns
+char(10)
+'from'
+space(1)
+@tablename
+space(1)
+char(10)
+'where'
+space(1)
+char(10)
+@klcolumns
+ char(10)
exec (@select)
print @select
/*---------------------------------------------------------------------------*//* select end *//*---------------------------------------------------------------------------*/ 
