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 'Employees','EmployeeID','UPD_Employees'

運行後產生如下所示的預存程序：

Create Procedure UPD_Employees

@EmployeeID int

@LastName nvarchar(40) ,

@FirstName nvarchar(20) ,

@Title nvarchar(60) ,

@TitleofCourtesy nvarchar(50) ,

@BirthDate datetime ,

@HireDate datetime ,

@Address nvarchar(120) ,

@City nvarchar(30) ,

@Region nvarchar(30) ,

@PostalCode nvarchar(20) ,

@Country nvarchar(30) ,

@HomePhone nvarchar(48) ,

@Extension nvarchar(8) ,

@Phote image ,

@Notes ntext ,

@ReportsTo int ,

@PhotoPath nvarchar(510)

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

使用以上的兩個預存程序，節省了我不少時間。特別是在改變了表結構後，重新構造各個預存程序的過程中。您可以改寫這兩個程式，來自動產生別的預存程序。

SQL Server編寫預存程序小工具
以下是兩個預存程序的來源程式
/*===========================================================

文法: sp_GenInsert <Table Name>,<Stored Procedure Name>
以northwind 資料庫為例
sp_GenInsert 'Employees', 'INS_Employees'

注釋：如果您在Master系統資料庫中建立該過程，那您就可以在您伺服器上所有的資料庫中使用該過程。

=============================================================*/

CREATE procedure sp_GenInsert
@TableName varchar(130),
@ProcedureName varchar(130)
as
set nocount on

declare @maxcol int,
@TableID int
--itlearner.com
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 '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

SQL Server編寫預存程序小工具
功能：為給定表建立Update預存程序
文法： sp_GenUpdate <Table Name>,<Primary Key>,<Stored Procedure Name>
以northwind 資料庫為例
sp_GenUpdate 'Employees','EmployeeID','UPD_Employees'

注釋：如果您在Master系統資料庫中建立該過程，那您就可以在您伺服器上所有的資料庫中使用該過程。

===========================================================*/
CREATE procedure sp_GenUpdate
@TableName varchar(130),
@PrimaryKey varchar(130),
@ProcedureName varchar(130)
as
set nocount on

declare @maxcol int,
@TableID int
--itlearner.com
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
/*＝＝＝＝＝＝＝來源程式結束＝＝＝＝＝＝＝＝＝*/