获取栏位参数列表

来源：互联网发布：扣字软件编辑：程序博客网时间：2024/04/30 09:23

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

效果如下图：