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,0as 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 1as colorder union select 'UPDATE ' @TableName,@maxcol 2as colorder union select 'SET',@maxcol 3as colorder union select syscolumns.name ' = @' syscolumns.name case when colorder < @maxcol then ',' when colorder = @maxcol then '' end as type, colorder @maxcol 3as 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) 4as colorder order by colorder select type from #tempproc order by colorder drop table #tempproc