SQL Server writes stored procedure gadgets (i)

server| Stored Procedures

Enet Institute of Technology, all rights reserved

In the process of developing a database system, many stored procedures are often written. To unify the format and simplify the development process, I write some stored procedures to automate the generation of stored procedures. Here's a brief introduction to them. One is used to generate the insert process and the other to generate the update process.

Sp_geninsert

After the procedure is run, it generates a complete insert process for the given table. If the original table has an identity column, you must manually delete the set Idntity_insert on statement in the generated procedure.

The syntax is as follows

Sp_geninsert < Table Name >,< Stored Procedure name >

Take the Northwind database as an example

Sp_geninsert ' Employees ', ' ins_employees '

An insert stored procedure is generated at the end. With it, you can make further development.

Sp_genupdate

It generates an update stored procedure for a table. The syntax is as follows:

Sp_genupdate < Table Name >,< Primary Key >,< Stored Procedure Name >

Take the Northwind database as an example

Sp_genupdate ' Employees ', ' EmployeeID ', ' upd_employees '

After the run, generate the stored procedure as shown below:

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 the two stored procedures above saves me a lot of time. In particular, after changing the table structure, the process of restructuring individual stored procedures. You can overwrite these two programs to automatically generate other stored procedures.