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 : https://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
This page was last updated on May 01, 2006 04:28 PM.