我的TSQL代码生成器
大家都应该用过代码生成器,像CodeSmith等,其实对咱们程序员而言,要生成代码其实是有很多办法的,比如,咱们就试试用TSQL生成TSQL的存储过程吧,直接上代码:
下面这段代码会根据Northwind中的Customers表生成一个更新数据的存储过程。大家可以临活发挥,比如写个游标遍历数据库中的所有表,然后生成CRUD所有的存储过程。。。
下面是生成的结果。。
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
下面这段代码会根据Northwind中的Customers表生成一个更新数据的存储过程。大家可以临活发挥,比如写个游标遍历数据库中的所有表,然后生成CRUD所有的存储过程。。。
Code
下面是生成的结果。。
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