SQL Server編寫存儲過程小工具

　　在開發數據庫系統的過程中經常要寫很多的存儲過程為了統一格式和簡化開發過程我編寫一些存儲過程用來自動生成存儲過程下面就為您簡單介紹一下它們其中一個用於生成Insert過程另一個用於生成Update過程
　　
　　
　　Sp_GenInsert
　　
　　該過程運行後它為給定的表生成一個完整的Insert過程如果原來的表有標識列您得將生成的過程中的SET IDNTITY_INSERT ON 語句手工刪除
　　
　　語法如下
　　
　　sp_GenInsert < Table Name >< Stored Procedure Name >
　　
　　以northwind 數據庫為例
　　
　　sp_GenInsert Employees INS_Employees
　　
　　最後會生成一個Insert存儲過程利用它您可以作進一步的開發
　　
　　
　　Sp_GenUpdate
　　
　　它會為一個表生成update存儲過程語法如下
　　
　　sp_GenUpdate < Table Name >< Primary Key >< Stored Procedure Name >
　　
　　以northwind 數據庫為例
　　
　　sp_GenUpdate EmployeesEmployeeIDUPD_Employees
　　
　　運行後生成如下所示的存儲過程
　　
　　Create Procedure UPD_Employees
　　
　　@EmployeeID int
　　
　　@LastName nvarchar()
　　
　　@FirstName nvarchar()
　　
　　@Title nvarchar()
　　
　　@TitleofCourtesy nvarchar()
　　
　　@BirthDate datetime
　　
　　@HireDate datetime
　　
　　@Address nvarchar()
　　
　　@City nvarchar()
　　
　　@Region nvarchar()
　　
　　@PostalCode nvarchar()
　　
　　@Country nvarchar()
　　
　　@HomePhone nvarchar()
　　
　　@Extension nvarchar()
　　
　　@Phote image
　　
　　@Notes ntext
　　
　　@ReportsTo int
　　
　　@PhotoPath nvarchar()
　　
　　AS
　　
　　UPDATE Employees
　　
　　SET
　　
　　LastName = @LastName
　　
　　FirstName = @FirstName
　　
　　Title = @Title
　　
　　TitleofCourtesy = @TitleofCourtesy
　　
　　BirthDate = @BirthDate
　　
　　HireDate = @HireDate
　　
　　Address = @Address
　　
　　City = @City
　　
　　Regin = @Regin
　　
　　PostalCode = @PostCode
　　
　　Country = @Country
　　
　　HomePhone = @HomePhone
　　
　　Extension = @Extension
　　
　　Photo = @Photo
　　
　　Notes = @Notes
　　
　　ReportsTo = @ReportsTo
　　
　　PhotoPath = @PhotoPath
　　
　　WHERE EmployeeID = @EmployeeID
　　
　　
　　使用以上的兩個存儲過程節省了我不少時間特別是在改變了表結構後重新構造各個存儲過程的過程中您可以改寫這兩個程序來自動生成別的存儲過程
　　以下是兩個存儲過程的源程序
　　/*==================================================================
　　
　　語法: sp_GenInsert
　　以northwind 數據庫為例
　　sp_GenInsert Employees INS_Employees
　　
　　注釋如果您在Master系統數據庫中創建該過程那您就可以在您服務器上所有的數據庫中使用該過程
　　
　　==================================================================*/
　　
　　CREATE procedure sp_GenInsert
　　@TableName varchar()
　　@ProcedureName varchar()
　　as
　　set nocount on
　　
　　declare @maxcol int
　　@TableID int
　　
　　set @TableID = object_id(@TableName)
　　
　　select @MaxCol = max(colorder)
　　from syscolumns
　　where id = @TableID
　　
　　select Create Procedure + rtrim(@ProcedureName) as type as colorder into #TempProc
　　union
　　select convert(char()@ + syscolumnsname)
　　+ rtrim(systypesname)
　　+ case when rtrim(systypesname) in (binarycharncharnvarcharvarbinaryvarchar) then ( + rtrim(convert(char()syscolumnslength)) + )
　　when rtrim(systypesname) not in (binarycharncharnvarcharvarbinaryvarchar) then
　　end
　　+ case when colorder < @maxcol then ','
　　when colorder = @maxcol then ' '
　　end
　　as type,
　　colorder
　　from syscolumns
　　join systypes on syscolumns.xtype = systypes.xtype
　　where id = @TableID and systypes.name <> 'sysname'
　　union
　　select 'AS',@maxcol + 1 as colorder
　　union
　　select 'INSERT INTO ' + @TableName,@maxcol + 2 as colorder
　　union
　　select '(',@maxcol + 3 as colorder
　　union
　　select syscolumns.name
　　+ case when colorder < @maxcol then ','
　　when colorder = @maxcol then ' '
　　end
　　as type,
　　colorder + @maxcol + 3 as colorder
　　from syscolumns
　　join systypes on syscolumns.xtype = systypes.xtype
　　where id = @TableID and systypes.name <> 'sysname'
　　union
　　select ')',(2 * @maxcol) + 4 as colorder
　　union
　　select 'VALUES',(2 * @maxcol) + 5 as colorder
　　union
　　select '(',(2 * @maxcol) + 6 as colorder
　　union
　　select '@' + syscolumns.name
　　+ case when colorder < @maxcol then ','
　　when colorder = @maxcol then ' '
　　end
　　as type,
　　colorder + (2 * @maxcol + 6) as colorder
　　from syscolumns
　　join systypes on syscolumns.xtype = systypes.xtype
　　where id = @TableID and systypes.name <> 'sysname'
　　union
　　select ')',(3 * @maxcol) + 7 as colorder
　　order by colorder
　　
　　
　　select type from #tempproc order by colorder
　　
　　drop table #tempproc
　　功能：為給定表創建Update存儲過程
　　語法： sp_GenUpdate ,,
　　以northwind 數據庫為例
　　sp_GenUpdate 'Employees','EmployeeID','UPD_Employees'
　　
　　注釋：如果您在Master系統數據庫中創建該過程，那您就可以在您服務器上所有的數據庫中使用該過程。tW.wINgwIT.cOm
　　
　　===========================================================*/
　　CREATE procedure sp_GenUpdate
　　@TableName varchar(130),
　　@PrimaryKey varchar(130),
　　@ProcedureName varchar(130)
　　as
　　set nocount on
　　
　　declare @maxcol int,
　　@TableID int
　　
　　set @TableID = object_id(@TableName)
　　
　　select @MaxCol = max(colorder)
　　from syscolumns
　　where id = @TableID
　　
　　select 'Create Procedure ' + rtrim(@ProcedureName) as type,0 as colorder into #TempProc
　　union
　　select convert(char(35),'@' + syscolumns.name)
　　+ rtrim(systypes.name)
　　+ case when rtrim(systypes.name) in ('binary','char','nchar','nvarchar','varbinary','varchar') then '(' + rtrim(convert(char(4),syscolumns.length)) + ')'
　　when rtrim(systypes.name) not in ('binary','char','nchar','nvarchar','varbinary','varchar') then ' '
　　end
　　+ case when colorder < @maxcol then ','
　　when colorder = @maxcol then ' '
　　end
　　as type,
　　colorder
　　from syscolumns
　　join systypes on syscolumns.xtype = systypes.xtype
　　where id = @TableID and systypes.name <> 'sysname'
　　union
　　select 'AS',@maxcol + 1 as colorder
　　union
　　select 'UPDATE ' + @TableName,@maxcol + 2 as colorder
　　union
　　select 'SET',@maxcol + 3 as colorder
　　union
　　select syscolumns.name + ' = @' + syscolumns.name
　　+ case when colorder < @maxcol then ','
　　when colorder = @maxcol then ' '
　　end
　　as type,
　　colorder + @maxcol + 3 as colorder
　　from syscolumns
　　join systypes on syscolumns.xtype = systypes.xtype
　　where id = @TableID and syscolumns.name <> @PrimaryKey and systypes.name <> 'sysname'
　　union
　　select 'WHERE ' + @PrimaryKey + ' = @' + @PrimaryKey,(2 * @maxcol) + 4 as colorder
　　order by colorder
　　
　　
　　select type from #tempproc order by colorder
　　
　　drop table #tempproc
　　/*＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝源程序結束＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝*/
　　

From:http://tw.wingwit.com/Article/program/SQLServer/201311/22023.html