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 @plisnullable char(3)
declare @pldata_type varchar(20)
declare @plcharacter_maximum_length int
declare @plparm varchar(2000)
-- end support for parm_listing
-- begin support for column_listing
declare @clcolumn_name varchar(128)
declare @clisnullable char(3)
declare @clcolumns varchar(2000)
-- end supoprt for column_listing
-- begin support for value_listing
declare @vlcolumn_name varchar(128)
declare @vlisnullable char(3)
declare @vlcolumns varchar(2000)
-- end supoprt for value_listing
-- begin support for set_listing
declare @slcolumn_name varchar(128)
declare @slisnullable char(3)
declare @slcolumns varchar(2000)
-- end support for set_listing
-- begin support for key_listing
declare @klcolumn_name varchar(128)
declare @klisnullable varchar(3)
declare @klcolumns varchar(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
for select column_name,
is_nullable,
data_type,
character_maximum_length
from information_schema.columns
where table_name = @tablename
order by ordinal_position
open parm_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 null then space(1)
when @plcharacter_maximum_length is not null and @plcharacter_maximum_length > 8000 then space(1)
else char(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
close parm_listing
deallocate parm_listing
/*---------------------------------------------------------------------------*/
/* parm_listing end */
/*---------------------------------------------------------------------------*/
column_listing:
/*---------------------------------------------------------------------------*/
/* column_listing begin */
/*---------------------------------------------------------------------------*/
set @clcolumns = ''
declare column_listing cursor
for select column_name,
is_nullable
from information_schema.columns
where table_name = @tablename
order by ordinal_position
open column_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
close column_listing
deallocate column_listing
/*---------------------------------------------------------------------------*/
/* column_listing end */
/*---------------------------------------------------------------------------*/
value_listing:
/*---------------------------------------------------------------------------*/
/* value_listing begin */
/*---------------------------------------------------------------------------*/
set @vlcolumns = ''
declare value_listing cursor
for select column_name,
is_nullable
from information_schema.columns
where table_name = @tablename
order by ordinal_position
open value_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
close value_listing
deallocate value_listing
/*---------------------------------------------------------------------------*/
/* value_listing end */
/*---------------------------------------------------------------------------*/
key_listing:
/*---------------------------------------------------------------------------*/
/* key_listing begin */
/*---------------------------------------------------------------------------*/
set @klcolumns = ''
declare key_listing cursor
for select keys.column_name
from information_schema.columns as cols
join information_schema.key_column_usage as keys
on cols.table_name = keys.table_name
and cols.column_name = keys.column_name
join information_schema.table_constraints as consts
on keys.constraint_name = consts.constraint_name
and consts.constraint_type = 'primary key'
where keys.table_name = @tablename
open key_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'
close key_listing
deallocate key_listing
/*---------------------------------------------------------------------------*/
/* key_listing end */
/*---------------------------------------------------------------------------*/
set_listing:
/*---------------------------------------------------------------------------*/
/* set_listing begin */
/*---------------------------------------------------------------------------*/
set @slcolumns = ''
declare set_listing cursor
for select column_name,
is_nullable
from information_schema.columns
where table_name = @tablename
order by ordinal_position
open set_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
close set_listing
deallocate set_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 */
/*---------------------------------------------------------------------------*/
