/*===========================================================
源代码网整理以下语法:sp_GenInsert<TableName>,<StoredProcedureName>
以northwind数据库为例
sp_GenInsert"Employees","INS_Employees"
源代码网整理以下注释：如果您在Master系统数据库中创建该过程，那您就可以在您服务器上所有的数据库中使用该过程。
源代码网整理以下=============================================================*/
源代码网整理以下CREATEproceduresp_GenInsert
@TableNamevarchar(130),
@ProcedureNamevarchar(130)
as
setnocounton
declare@maxcolint,
@TableIDint
set@TableID=object_id(@TableName)
select@MaxCol=max(colorder)
fromsyscolumns
whereid=@TableID
select"CreateProcedure"+rtrim(@ProcedureName)astype,0ascolorderinto#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
+casewhencolorder<@maxcolthen","
whencolorder=@maxcolthen""
end
astype,
colorder
fromsyscolumns
joinsystypesonsyscolumns.xtype=systypes.xtype
whereid=@TableIDandsystypes.name<>"sysname"
union
select"AS",@maxcol+1ascolorder
union
select"INSERTINTO"+@TableName,@maxcol+2ascolorder
union
select"(",@maxcol+3ascolorder
union
selectsyscolumns.name
+casewhencolorder<@maxcolthen","
whencolorder=@maxcolthen""
end
astype,
colorder+@maxcol+3ascolorder
fromsyscolumns
joinsystypesonsyscolumns.xtype=systypes.xtype
whereid=@TableIDandsystypes.name<>"sysname"
union
select")",(2*@maxcol)+4ascolorder
union
select"VALUES",(2*@maxcol)+5ascolorder
union
select"(",(2*@maxcol)+6ascolorder
union
select
源代码网整理以下
selecttypefrom#tempprocorderbycolorder
droptable#tempproc
|
