下面这段代码会根据Northwind中的Customers表生成一个更新数据的存储过程。大家可以临活发挥,比如写个游标遍历数据库中的所有表,然后生成CRUD所有的存储过程。。。
Code
/*
Tony's litte cute TSQL Code Generator
*/
USE NORTHWIND
DECLARE @TableName NVARCHAR(200)
DECLARE @Parameters VARCHAR(8000)
DECLARE @UpdateFields VARCHAR(8000)
DECLARE @PrimaryKey NVARCHAR(200)
SELECT @TableName = 'Customers',@Parameters = '', @UpdateFields = ''
SELECT @PrimaryKey = CCU.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU ON TC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME
WHERE TC.TABLE_NAME = @TableName AND TC.CONSTRAINT_TYPE='PRIMARY KEY'
PRINT 'CREATE PROCEDURE dbo.usp_Update' + @TableName + 'ByPK'
SELECT @Parameters = @Parameters + ' @'+COLUMN_NAME+' '+(
CASE WHEN DATA_TYPE='NVARCHAR' OR DATA_TYPE='VARCHAR' OR DATA_TYPE='CHAR'OR DATA_TYPE='NCHAR'
THEN DATA_TYPE+ '('+CAST(CHARACTER_MAXIMUM_LENGTH AS NVARCHAR(20))+')'
ELSE
DATA_TYPE
END)+','+CHAR(10)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName --AND COLUMN_NAME != @PrimaryKey
PRINT LEFT(@Parameters,LEN(@Parameters)-2)
PRINT 'AS'+CHAR(10)+'BEGIN'
PRINT ' UPDATE ' + @TableName
PRINT ' SET'
SELECT @UpdateFields = @UpdateFields +' '+COLUMN_NAME +' = @'+ COLUMN_NAME +','+ CHAR(10)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName AND COLUMN_NAME != @PrimaryKey
PRINT LEFT(@UpdateFields,LEN(@UpdateFields) -2)
PRINT ' WHERE'
PRINT ' '+ @PrimaryKey +' = @' + @PrimaryKey
PRINT 'END'
下面是生成的结果。。
CREATE PROCEDURE dbo.usp_UpdateOrdersByPK
@OrderID int,
@CustomerID nchar,
@EmployeeID int,
@OrderDate datetime,
@RequiredDate datetime,
@ShippedDate datetime,
@ShipVia int,
@Freight money,
@ShipName nvarchar (40),
@ShipAddress nvarchar (60),
@ShipCity nvarchar (15),
@ShipRegion nvarchar (15),
@ShipPostalCode nvarchar (10),
@ShipCountry nvarchar (15)
AS
BEGIN
UPDATE Orders
SET
CustomerID = @CustomerID,
EmployeeID = @EmployeeID,
OrderDate = @OrderDate,
RequiredDate = @RequiredDate,
ShippedDate = @ShippedDate,
ShipVia = @ShipVia,
Freight = @Freight,
ShipName = @ShipName,
ShipAddress = @ShipAddress,
ShipCity = @ShipCity,
ShipRegion = @ShipRegion,
ShipPostalCode = @ShipPostalCode,
ShipCountry = @ShipCountry
WHERE
OrderID = @OrderID
END
转载于:https://www.cnblogs.com/tonywoo/archive/2009/07/30/1535530.html