获取栏位参数列表

平时总会写一些存储过程，有的参数基本上是和表的栏位一一对应，如果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

效果如下图：