SQL Server tool for writing stored procedures (1)

SQL Server tool for writing stored procedures

The enet Technical College is copyrighted. No one can copy it without permission! Those who violate the rules are required !!

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 these two programs to automatically generate other stored procedures.