在开发数据库系统的过程中，经常要写很多的存储过程。为了统一格式和简化开发过程，我编写一些存储过程，用来自动生成存储过程。下面就为您简单介绍一下它们。其中一个用于生成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 /*＝＝＝＝＝＝＝源程序结束＝＝＝＝＝＝＝＝＝*/