Technical Article

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                                                             *//*---------------------------------------------------------------------------*/

Rate

1 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

1 (1)

You rated this post out of 5. Change rating