SQLServer编写存储过程小工具(三)

来源：互联网发布：网络综合布线施工方案编辑：程序博客网时间：2024/04/30 16:39

编写存储过程小工具
功能：为给定表创建Update存储过程
语法：sp_GenUpdate<TableName>,<PrimaryKey>,<StoredProcedureName>
以northwind为例
sp_GenUpdate'Employees','EmployeeID','UPD_Employees'

注释：如果您在Master系统数据库中创建该过程，那您就可以在您服务器上所有的数据库中使用该过程。

===========================================================*/
CREATEproceduresp_GenUpdate
@TableNamevarchar(130),
@PrimaryKeyvarchar(130),
@ProcedureNamevarchar(130)
as
setnocounton

declare@maxcolint,
@TableIDint
'acnow.net
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'UPDATE'+@TableName,@maxcol+2ascolorder
union
select'SET',@maxcol+3ascolorder
union
selectsyscolumns.name+'=@'+syscolumns.name
+casewhencolorder<@maxcolthen','
whencolorder=@maxcolthen''
end
astype,
colorder+@maxcol+3ascolorder
fromsyscolumns
joinsystypesonsyscolumns.xtype=systypes.xtype
whereid=@TableIDandsyscolumns.name<>@PrimaryKeyandsystypes.name<>'sysname'
union
select'WHERE'+@PrimaryKey+'=@'+@PrimaryKey,(2*@maxcol)+4ascolorder
orderbycolorder

selecttypefrom#tempprocorderbycolorder

droptable#tempproc
/*＝＝＝＝＝＝＝源程序结束＝＝＝＝＝＝＝＝＝*/