SQL SERVER系統(tǒng)表應(yīng)用之基于Table生成存儲過程參數(shù)列表

有時一個Table有很多列，你需要寫一個存儲過程，那個Table的參數(shù)列表夠你寫的了。我們可以利用系統(tǒng)表生成這個列表，看T-SQL:

select '@' + c.name, col_definition =
case t.name
when 'bigint' then 'bigint,'
when 'int' then 'int,'
when 'smallint' then 'smallint,'
when 'tinyint' then 'tinyint,'
when 'bit' then 'bit,'
when 'decimal' then 'decimal,'
when 'numeric' then 'numeric,'
when 'money' then 'money,'
when 'smallmoney' then 'smallmoney,'
when 'float' then 'float,'
when 'real' then 'real,'
when 'datetime' then 'datetime,'
when 'smalldatetime' then 'smalldatetime,'
when 'sql_variant' then 'sql_variant,'
when 'timestamp' then 'timestamp,'
when 'uniqueidentifier' then 'uniqueidentifier,'
when 'xml' then 'xml,'
else t.name + '(' + cast(c.prec as varchar(50)) + '),'
end
from sysobjects s
inner join sys.syscolumns c on s.id = c.id
inner join sys.types t on t.user_type_id = c.xtype
where s.xtype='U'
and s.name = 'Product'

結(jié)果是：

(No column name) col_definition
@ProductID int,
@Name nvarchar(50),
@ProductNumber nvarchar(25),
@MakeFlag bit,
@FinishedGoodsFlag bit,
@Color nvarchar(15),
@SafetyStockLevel smallint,
@ReorderPoint smallint,
@StandardCost money,
@ListPrice money,
@Size nvarchar(5),
@SizeUnitMeasureCode nchar(3),
@WeightUnitMeasureCode nchar(3),
@Weight decimal,
@DaysToManufacture int,
@ProductLine nchar(2),
@Class nchar(2),
@Style nchar(2),
@ProductSubcategoryID int,
@ProductModelID int,
@SellStartDate datetime,
@SellEndDate datetime,
@DiscontinuedDate datetime,
@rowguid uniqueidentifier,
@ModifiedDate datetime,

這里我使用的Table來自AdventureWorks，完了，這么簡單。

Author: Petter Liu http://wintersun.cnblogs.com