「钢铁板块龙头股」SQLServer编写存储过程小工具(三)

SQLServer编写存储进程小工具

功用：为给定表创立Update存储进程

语法：sp_GenUpdate,

,

以norpwind数据库为例

sp_GenUpdate'Employees','EmployeeID','UPD_Employees'

注释：假如您在Master体系数据库中创立该进程，那您就能够在您服务器上一切的数据库中运用该进程。

/*===========================================================*/

CREATEproceduresp_GenUpdate

@TableNamevarchar(130),

@PrimaryKeyvarchar(130),

@ProcedureNamevarchar(130)

as

setnocounton

declare@maxcolint,

@TableIDint

'knowsky

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')pen'('+rtrim(convert(char(4),syscolumns.lengp))+')'

whenrtrim(systypes.name)notin('binary'口碑营销策划计划",'char','nchar','nvarchar','varbinary','varchar')pen''

end

+casewhencolorder<@maxcolpen','

whencolorder=@maxcolpen''

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<@maxcolpen','

whencolorder=@maxcolpen''

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

/*＝＝＝＝＝＝＝源程序完毕＝＝＝＝＝＝＝＝＝*/

本文来自:软文ruanwen.tingclouds