SQL Server tool for writing stored procedures
The following are the source code of the two stored procedures:
/* ===================================================== ======================================
Syntax: sp_geninsert < Table Name > , < Stored Procedure name >
Take the northwind database as an Example
Sp_geninsert 'ployees', 'ins _ 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_geninsert
@ Tablename 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 'insert into' + @ tablename, @ maxcol + 2 as colorder
Union
Select '(', @ maxcol + 3 as colorder
Union
Select 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 policypes. Name < > 'Sysname'
Union
Select ')', (2 * @ maxcol) + 4 as colorder
Union
Select 'values', (2 * @ maxcol) + 5 as colorder
Union
Select '(', (2 * @ maxcol) + 6 as colorder
Union
Select '@' + syscolumns. Name
+ Case when colorder < @ Maxcol Then ','
When colorder = @ Maxcol Then''
End
As type,
Colorder + (2 * @ maxcol + 6) as colorder
From syscolumns
Join policypes on syscolumns. xtype = Policypes. xtype
Where ID = @ Tableid And policypes. Name < > 'Sysname'
Union
Select ')', (3 * @ maxcol) + 7 as colorder
Order by colorder
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.