SQL Server tool for writing stored procedures (3)

SQL Server tool for writing stored procedures
Function: creates 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 'ployees', 'employeeid', 'upd _ Employees'

Note: If you create this process in the Master System database, you can use this process in all the 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

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 (35), '@' + syscolumns. Name)
+ Rtrim (policypes. Name)
+ Case when rtrim (policypes. name) in ('binary ', 'Char', 'nchar ', 'nvarchar', 'varbinary ', 'varchar ') then' ('+ rtrim (convert (char (4), syscolumns. length) + ')'
When rtrim (policypes. Name) not 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 policypes on syscolumns. xtype = Policypes. xtype
Where ID = @ Tableid And policypes. 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 policypes on syscolumns. xtype = Policypes. xtype
Where ID = @ Tableid And syscolumns. Name < > @ Primarykey and policypes. 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
/* ===================== end of the source program =================================*/< BR>