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>
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.