我找到了两个存储过程，能自动生成对一个数据表的插入和更新的存储过程，现在奉献给大家！
插入：
Create procedure sp_GenInsert@TableName varchar(130),@ProcedureName varchar(130)asset nocount ondeclare @maxcol int,@TableID intset @TableID = object_id(@TableName)select @MaxCol = max(colorder)from syscolumnswhere id = @TableIDselect 'Create Procedure ' + rtrim(@ProcedureName) as type,0 as colorder into #TempProcunionselect 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 ' 'endas type,colorderfrom syscolumnsjoin systypes on syscolumns.xtype = systypes.xtypewhere id = @TableID and systypes.name <> 'sysname'unionselect 'AS',@maxcol + 1 as colorderunionselect 'INSERT INTO ' + @TableName,@maxcol + 2 as colorderunionselect '(',@maxcol + 3 as colorderunionselect syscolumns.name+ case when colorder < @maxcol then ',' when colorder = @maxcol then ' 'endas type,colorder + @maxcol + 3 as colorderfrom syscolumnsjoin systypes on syscolumns.xtype = systypes.xtypewhere id = @TableID and systypes.name <> 'sysname'unionselect ')',(2 * @maxcol) + 4 as colorderunionselect 'VALUES',(2 * @maxcol) + 5 as colorderunionselect '(',(2 * @maxcol) + 6 as colorderunionselect '@' + syscolumns.name+ case when colorder < @maxcol then ',' when colorder = @maxcol then ' 'endas type,colorder + (2 * @maxcol + 6) as colorderfrom syscolumnsjoin systypes on syscolumns.xtype = systypes.xtypewhere id = @TableID and systypes.name <> 'sysname'unionselect ')',(3 * @maxcol) + 7 as colorderorder by colorderselect type from #tempproc order by colorder
更新：
Create procedure sp_GenUpdate@TableName varchar(130),@PrimaryKey varchar(130),@ProcedureName varchar(130)asset nocount ondeclare @maxcol int,@TableID intset @TableID = object_id(@TableName)select @MaxCol = max(colorder)from syscolumnswhere id = @TableIDselect 'Create Procedure ' + rtrim(@ProcedureName) as type,0 as colorder into #TempProcunionselect 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 ' 'endas type,colorderfrom syscolumnsjoin systypes on syscolumns.xtype = systypes.xtypewhere id = @TableID and systypes.name <> 'sysname'unionselect 'AS',@maxcol + 1 as colorderunionselect 'UPDATE ' + @TableName,@maxcol + 2 as colorderunionselect 'SET',@maxcol + 3 as colorderunionselect syscolumns.name + ' = @' + syscolumns.name+ case when colorder < @maxcol then ',' when colorder = @maxcol then ' 'endas type,colorder + @maxcol + 3 as colorderfrom syscolumnsjoin systypes on syscolumns.xtype = systypes.xtypewhere id = @TableID and syscolumns.name <> @PrimaryKey and systypes.name <> 'sysname'unionselect 'WHERE ' + @PrimaryKey + ' = @' + @PrimaryKey,(2 * @maxcol) + 4 as colorderorder by colorderselect type from #tempproc order by colorderdrop table #tempproc
