在開(kāi)發(fā)數(shù)據(jù)庫(kù)系統(tǒng)的過(guò)程中，經(jīng)常要寫(xiě)很多的存儲(chǔ)過(guò)程。為了統(tǒng)一格式和簡(jiǎn)化開(kāi)發(fā)過(guò)程，我編寫(xiě)一些存儲(chǔ)過(guò)程，用來(lái)自動(dòng)生成存儲(chǔ)過(guò)程。下面就為您簡(jiǎn)單介紹一下它們。其中一個(gè)用于生成Insert過(guò)程，另一個(gè)用于生成Update過(guò)程。 


Sp_GenInsert 

該過(guò)程運(yùn)行后，它為給定的表生成一個(gè)完整的Insert過(guò)程。如果原來(lái)的表有標(biāo)識(shí)列，您得將生成的過(guò)程中的SET IDNTITY_INSERT ON 語(yǔ)句手工刪除。 

語(yǔ)法如下 

sp_GenInsert < Table Name >,< Stored Procedure Name > 

以northwind 數(shù)據(jù)庫(kù)為例 

sp_GenInsert 'Employees', 'INS_Employees' 

最后會(huì)生成一個(gè)Insert存儲(chǔ)過(guò)程。利用它，您可以作進(jìn)一步的開(kāi)發(fā)。 


Sp_GenUpdate 

它會(huì)為一個(gè)表生成update存儲(chǔ)過(guò)程。語(yǔ)法如下： 

sp_GenUpdate < Table Name >,< Primary Key >,< Stored Procedure Name > 

以northwind 數(shù)據(jù)庫(kù)為例 

sp_GenUpdate 'Employees','EmployeeID','UPD_Employees' 

運(yùn)行后生成如下所示的存儲(chǔ)過(guò)程： 

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 


使用以上的兩個(gè)存儲(chǔ)過(guò)程，節(jié)省了我不少時(shí)間。特別是在改變了表結(jié)構(gòu)后，重新構(gòu)造各個(gè)存儲(chǔ)過(guò)程的過(guò)程中。您可以改寫(xiě)這兩個(gè)程序，來(lái)自動(dòng)生成別的存儲(chǔ)過(guò)程。

SQL Server編寫(xiě)存儲(chǔ)過(guò)程小工具
以下是兩個(gè)存儲(chǔ)過(guò)程的源程序
/*===========================================================

語(yǔ)法: sp_GenInsert <Table Name>,<Stored Procedure Name>
以northwind 數(shù)據(jù)庫(kù)為例
sp_GenInsert 'Employees', 'INS_Employees'

注釋：如果您在Master系統(tǒng)數(shù)據(jù)庫(kù)中創(chuàng)建該過(guò)程，那您就可以在您服務(wù)器上所有的數(shù)據(jù)庫(kù)中使用該過(guò)程。

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

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編寫(xiě)存儲(chǔ)過(guò)程小工具
功能：為給定表創(chuàng)建Update存儲(chǔ)過(guò)程
語(yǔ)法： sp_GenUpdate <Table Name>,<Primary Key>,<Stored Procedure Name>
以northwind 數(shù)據(jù)庫(kù)為例
sp_GenUpdate 'Employees','EmployeeID','UPD_Employees'

注釋：如果您在Master系統(tǒng)數(shù)據(jù)庫(kù)中創(chuàng)建該過(guò)程，那您就可以在您服務(wù)器上所有的數(shù)據(jù)庫(kù)中使用該過(guò)程。

===========================================================*/
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
/*＝＝＝＝＝＝＝源程序結(jié)束＝＝＝＝＝＝＝＝＝*/

 

最新評(píng)論