SQL Server tool for writing stored procedures

Many stored procedures are often written during database system development. To unify the format and simplify the development process, I wrote some stored procedures to automatically generate stored procedures. The following is a brief introduction to them. One is used to generate the Insert Process, and the other is used to generate the update process.

Sp_geninsert

After this process is run, it generates a complete Insert Process for the given table. If the original table has an ID column, you must manually delete the set idntity_insert on statement generated during the process.

Syntax:

Sp_geninsert <Table Name>, <Stored Procedure Name>

Take the northwind database as an Example

Sp_geninsert 'ployees', 'ins _ Employees'

An Insert stored procedure is generated. You can use it for further development.

Sp_genupdate

It generates an update stored procedure for a table. Syntax:

Sp_genupdate <Table Name>, <primary key>, <Stored Procedure Name>

Take the northwind database as an Example

Sp_genupdate 'ployees', 'employeeid', 'upd _ Employees'

The following stored procedure is generated after running:

Create procedure upd_employees

@ Employeeid int

@ Lastname nvarchar (40 ),

@ Firstname nvarchar (20 ),

@ Title nvarchar (60 ),

@ Titleofcourtesy nvarchar (50 ),

@ Birthdate datetime,

@ Hiredate datetime,

@ Address nvarchar (120 ),

@ City nvarchar (30 ),

@ Region nvarchar (30 ),

@ Postalcode nvarchar (20 ),

@ Country nvarchar (30 ),

@ Homephone nvarchar (48 ),

@ Extension nvarchar (8 ),

@ Phote image,

@ Notes ntext,

@ Reportsto int,

@ Photopath nvarchar (510)

As

Update employees

Set

Lastname = @ lastname,

Firstname = @ firstname,

Title = @ title,

Titleofcourtesy = @ titleofcourtesy,

Birthdate = @ birthdate,

Hiredate = @ hiredate,

Address = @ address,

City = @ city,

Regin = @ regin,

Postalcode = @ postcode,

Country = @ country,

Homephone = @ homephone,

Extension = @ extension,

Photo = @ photo

Notes = @ notes,

Reportsto = @ reportsto,

Photopath = @ photopath

Where employeeid = @ employeeid

Using these two stored procedures saves me a lot of time. Especially when the table structure is changed and each stored procedure is re-constructed. You can rewrite the twoProgramTo automatically generate other stored procedures.

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
--Itlearner.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 (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

Select Type from # tempproc order by colorder

Drop table # tempproc

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
--Itlearner.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 (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 =========== */