平时总会写一些存储过程,有的参数基本上是和表的栏位一一对应,如果C/P,既麻烦又容易出错,下面的SP可以生成指定表的栏位和参数列表:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
--exec CassabaBuildSP 'dbo.Categories'
CREATE procedure CassabaBuildSP
@tablename sysname
as
begin
declare @numtypes nvarchar(80)
select @numtypes = N'[tinyint],[smallint],[decimal],[real],[money],[float],[numeric],[smallmoney]'
SELECT NAME AS ColumnName, '['+ NAME + '], 'as BracketedColumn,'@' + NAME + ' ' + upper(type_name(xusertype)) +
case
when type_name(xtype) in('varchar','char')
then '(' + convert(varchar(5),length) + ')'
when type_name(xtype) in('nvarchar','nchar')
then '('+ convert(varchar(5),length/2) + ')'
when charindex('[' + type_name(xtype) + ']', @numtypes) > 0
then '(' + convert(varchar(5),ColumnProperty(id, name, 'precision')) + ','
else '' end
+
case
when charindex('[' + type_name(xtype) + ']', @numtypes) > 0
then convert(varchar(5),OdbcScale(xtype,xscale)) + ')'
else '' end
+ ',' as SPParameters,
'[' + NAME + '] = @' + NAME + ',' as UpdateSQL
FROM SYSCOLUMNS WHERE ID=OBJECT_ID(@tablename) ORDER BY COLID
end
效果如下图: