create procedure sp_GenInsert /**//* 功能描述:自动生成对数据表进行插入的存储过程的存储过程 */ ( @TableName 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 'INSERT INTO ' @TableName,@maxcol 2as colorder union select '(',@maxcol 3as colorder union select 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 systypes.name <>'sysname' union select ')',(2* @maxcol) 4as colorder union select 'VALUES',(2* @maxcol) 5as colorder union select '(',(2* @maxcol) 6as colorder union select [url=mailto:]'@'[/url] 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) 7as colorder order by colorder select type from #tempproc order by colorder