Auto generating Templates

				
--**************************************
-- Name: Auto generating Templates
-- Description:generate INSERT, UPDATE & DELETE statement templates for a table
-- By: Umachandar
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=234&lngWId=5--for details.--**************************************

Use master
go
if exists (select * from sysobjects where id = object_id('dbo.sp_gensp') and sysstat & 0xf = 4)
	drop procedure dbo.sp_gensp
GO
create procedure sp_gensp (@tvcTableName varchar(30), @tvcInsUpdDel varchar(3))
as
/********************************************************************************/
/*	Created By :	Umachandar Jayachandran	(UC)				*/
/*	Created On :	01 June 1996						*/
/*	Description:	This stored procedure allows generates simple SPs for	*/
/*			each DML statement viz. INSERT, UPDATE & DELETE excl.	*/
/*			SELECT statement.					*/
/********************************************************************************/
/*	Resources :	http://www.umachandar.com/resources.htm 	*/
/********************************************************************************/
declare @text varchar(255), @col_name varchar(32), @var_name varchar(255),
	@type_name varchar(13), @lnrowcount smallint, @lnnumcols smallint, @len int,
	@text1 varchar(255)
set nocount on
if not @tvcInsUpdDel in ('Ins', 'Upd', 'Del')
begin
	raiserror('Invalid sp type passed.', 1, 2)
	return (-1)
end
select @lnrowcount = 0
create table #columns (TABLE_QUALIFIER	varchar(32) NULL, 
TABLE_OWNER varchar(32), TABLE_NAME varchar(32), 
COLUMN_NAME varchar(32), DATA_TYPE smallint NULL, 
TYPE_NAME varchar(13), PREC int, 
LENGTH int, SCALE smallint NULL, RADIX smallint NULL, 
NULLABLE smallint, REMARKS varchar(254) NULL, 
COLUMN_DEF varchar(254) NULL, SQL_DATA_TYPE smallint,
SQL_DATETIME_SUB smallint NULL, CHAR_OCTET_LENGTH int NULL, ORDINAL_POSITION int, 
IS_NULLABLE varchar(254),
SS_DATA_TYPE tinyint)
insert #columns exec sp_columns @tvcTableName
if exists(select * from #columns where TABLE_OWNER = USER)
	delete #columns where TABLE_OWNER <> USER
else
	delete #columns where TABLE_OWNER <> USER and TABLE_OWNER <> 'dbo'
select @text = 'IF OBJECT_ID(''' + @tvcInsUpdDel + @tvcTableName + ''') IS NOT NULL'
print @text
select @text = ' DROP PROCEDURE ' + @tvcInsUpdDel + @tvcTableName
print @text
print 'GO'
select @text = 'CREATE PROCEDURE ' + @tvcInsUpdDel + @tvcTableName
print @text
print '('
if @tvcInsUpdDel = 'Del'
begin
	select @col_name = COLUMN_NAME, @type_name = TYPE_NAME
	from #columns
	where ORDINAL_POSITION = 1
	select @var_name = '@t' + case @type_name
					when 'int' then 'n'
					when 'char' then 'c'
					when 'varchar' then 'vc'
					when 'text' then 't'
					when 'datetime' then 'd'
					when 'float' then 'f'
					else 'u'
				 end
	select @text = @var_name + @col_name + SPACE(1) + @type_name
	print @text
	print ')'
	print 'AS'
	select @text = 'DELETE ' + @tvcTableName + ' WHERE ' + @col_name +
			' = ' + @var_name + @col_name
	print @text
	print 'GO'
end
if @tvcInsUpdDel in ('Ins', 'Upd')
begin
	select @lnnumcols = count(COLUMN_NAME) from #columns
	declare cols scroll cursor for select COLUMN_NAME, TYPE_NAME, LENGTH from #columns
	open cols
	fetch next from cols into @col_name, @type_name, @len
	while(@@fetch_status >= 0)
	begin
		select @lnrowcount = @lnrowcount + 1
		select @var_name = '@t' + case @type_name
						when 'int' then 'n'
						when 'char' then 'c'
						when 'varchar' then 'vc'
						when 'text' then 't'
						when 'datetime' then 'd'
						when 'float' then 'f'
						else 'u'
					end
		select @text = @var_name + @col_name + SPACE(1) + @type_name +
				case when @type_name in ('char', 'varchar')
					then '(' + ltrim(str(@len)) + ')'
					else null
				end +
				case when @lnrowcount = @lnnumcols then null else ',' end
		print @text
		fetch next from cols into @col_name, @type_name, @len
	end
	print ')'
	print 'AS'
end
if @tvcInsUpdDel = 'Ins'
begin
	select @text = 'INSERT INTO ' + @tvcTableName
	print @text
	print '('
	-- loop for select list
	select @lnrowcount = 0
	fetch first from cols into @col_name, @type_name, @len
	while(@@fetch_status >= 0)
	begin
		select @lnrowcount = @lnrowcount + 1
		select @text = @col_name + case when @lnrowcount = @lnnumcols then null else ',' end
		print @text
		fetch next from cols into @col_name, @type_name, @len
	end
	print ')'
	print 'VALUES'
	print '('
	-- loop for select list
	select @lnrowcount = 0
	fetch first from cols into @col_name, @type_name, @len
	while(@@fetch_status >= 0)
	begin
		select @lnrowcount = @lnrowcount + 1
		select @var_name = '@t' + case @type_name
						when 'int' then 'n'
						when 'char' then 'c'
						when 'varchar' then 'vc'
						when 'text' then 't'
						when 'datetime' then 'd'
						when 'float' then 'f'
						else 'u'
					 end
		select @text = @var_name + @col_name +
				case when @lnrowcount = @lnnumcols then null else ',' end
		print @text
		fetch next from cols into @col_name, @type_name, @len
	end
	print ')'
	print 'GO'
end
if @tvcInsUpdDel = 'Upd'
begin
	select @text = 'UPDATE ' + @tvcTableName
	print @text
	print 'SET'
	select @lnrowcount = 0
	-- goto to the first column
	fetch first from cols into @col_name, @type_name, @len
	select @var_name = '@t' + case @type_name
					when 'int' then 'n'
					when 'char' then 'c'
					when 'varchar' then 'vc'
					when 'text' then 't'
					when 'datetime' then 'd'
					when 'float' then 'f'
					else 'u'
				 end
	select @text1 = 'WHERE ' + @col_name + ' = ' + @var_name + @col_name
	-- start from next column
	fetch next from cols into @col_name, @type_name, @len
	while(@@fetch_status >= 0)
	begin
		select @lnrowcount = @lnrowcount + 1
		select @text = @col_name
		select @var_name = '@t' + case @type_name
						when 'int' then 'n'
						when 'char' then 'c'
						when 'varchar' then 'vc'
						when 'text' then 't'
						when 'datetime' then 'd'
						when 'float' then 'f'
						else 'u'
					 end
		select @text = @text + ' = ' + @var_name + @col_name +
				case when @lnrowcount = @lnnumcols then null else ',' end
		print @text
		fetch next from cols into @col_name, @type_name, @len
	end
	print @text1
	print 'GO'
end
close cols
deallocate cols
GO
GRANT EXECUTE ON dbo.sp_gensp TO public
GO