/**//*===============源程序開始==================*/ CREATEprocedure sp_GenInsert @TableNamevarchar(130), @ProcedureNamevarchar(130) as set nocount on declare@maxcolint, @TableIDint 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 selectconvert(char(35),'@'+ syscolumns.name) +rtrim(systypes.name) +casewhenrtrim(systypes.name) in ('binary','char','nchar','nvarchar','varbinary','varchar') then'('+rtrim(convert(char(4),syscolumns.length)) +')' whenrtrim(systypes.name) notin ('binary','char','nchar','nvarchar','varbinary','varchar') then'' end +casewhen colorder <@maxcolthen',' when colorder =@maxcolthen'' end as type, colorder from syscolumns join systypes on syscolumns.xtype = systypes.xtype where id =@TableIDand 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 +casewhen colorder <@maxcolthen',' when colorder =@maxcolthen'' end as type, colorder +@maxcol+3as colorder from syscolumns join systypes on syscolumns.xtype = systypes.xtype where id =@TableIDand systypes.name <>'sysname' union select')',(2*@maxcol) +4as colorder union select'VALUES',(2*@maxcol) +5as colorder union select'(',(2*@maxcol) +6as colorder union select'@'+ syscolumns.name +casewhen colorder <@maxcolthen',' when colorder =@maxcolthen'' end as type, colorder + (2*@maxcol+6) as colorder from syscolumns join systypes on syscolumns.xtype = systypes.xtype where id =@TableIDand systypes.name <>'sysname' union select')',(3*@maxcol) +7as colorder orderby colorder select type from #tempproc orderby colorder droptable #tempproc /**//*===============源程序結束==================*/ GO
/**//*===============源程序開始==================*/ CREATEprocedure sp_GenUpdate @TableNamevarchar(130), @PrimaryKeyvarchar(130), @ProcedureNamevarchar(130) as set nocount on declare@maxcolint, @TableIDint 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 selectconvert(char(35),'@'+ syscolumns.name) +rtrim(systypes.name) +casewhenrtrim(systypes.name) in ('binary','char','nchar','nvarchar','varbinary','varchar') then'('+rtrim(convert(char(4),syscolumns.length)) +')' whenrtrim(systypes.name) notin ('binary','char','nchar','nvarchar','varbinary','varchar') then'' end +casewhen colorder <@maxcolthen',' when colorder =@maxcolthen'' end as type, colorder from syscolumns join systypes on syscolumns.xtype = systypes.xtype where id =@TableIDand 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 +casewhen colorder <@maxcolthen',' when colorder =@maxcolthen'' end as type, colorder +@maxcol+3as colorder from syscolumns join systypes on syscolumns.xtype = systypes.xtype where id =@TableIDand syscolumns.name <>@PrimaryKeyand systypes.name <>'sysname' union select'WHERE '+@PrimaryKey+' = @'+@PrimaryKey,(2*@maxcol) +4as colorder orderby colorder select type from #tempproc orderby colorder droptable #tempproc /**//*===============源程序結束==================*/ GO