SQL Server writes a stored procedure gadget (iii)

server| Stored Procedures

SQL Server writes stored procedure gadgets
Function: Create an update stored procedure for a given table
Syntax: sp_genupdate <table name>,<primary key>,<stored Procedure name>
Take the Northwind database as an example
Sp_genupdate ' Employees ', ' EmployeeID ', ' upd_employees '

Note: If you create this procedure in the Master System database, you can use the procedure in all databases on your server.

===========================================================*/
CREATE procedure Sp_genupdate
@TableName varchar (130),
@PrimaryKey varchar (130),
@ProcedureName varchar (130)
As
SET NOCOUNT ON

DECLARE @maxcol int,
@TableID int
' Knowsky.com
Set @TableID = object_id (@TableName)

Select @MaxCol = max (Colorder)
From syscolumns
WHERE id = @TableID

Select ' Create Procedure ' + rtrim (@ProcedureName) as type,0 as Colorder into #TempProc
Union
Select CONVERT (char (), ' @ ' + 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) isn't 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 + 1 as Colorder
Union
Select ' UPDATE ' + @TableName, @maxcol + 2 as Colorder
Union
Select ' SET ', @maxcol + 3 as Colorder
Union
Select Syscolumns.name + ' = @ ' + syscolumns.name
+ Case When Colorder < @maxcol then ', '
When colorder = @maxcol Then '
End
As type,
Colorder + @maxcol + 3 as Colorder
From syscolumns
Join systypes on syscolumns.xtype = Systypes.xtype
WHERE id = @TableID and syscolumns.name <> @PrimaryKey and systypes.name <> ' sysname '
Union
Select ' WHERE ' + @PrimaryKey + ' = @ ' + @PrimaryKey, (2 * @maxcol) + 4 as Colorder
ORDER BY Colorder

Select type from #tempproc order by Colorder

drop table #tempproc
/*======= source program End =========*/