ENet Institute of Technology, All Rights Reserved
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.
