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

Rate

1 (1)

Share

Share

Rate

1 (1)