Home >Backend Development >PHP Tutorial >Small tool for writing stored procedures in SQL Server (1)_PHP tutorial
Small tool for writing stored procedures in SQL Server (1)_PHP tutorial
- WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOriginal
- 2016-07-13 17:00:21943browse
eNet Technology Institute, All Rights Reserved
In the process of developing a database system, it is often necessary to write a lot of stored procedures. In order to unify the format and simplify the development process, I wrote some stored procedures to automatically generate stored procedures. Below I will give you a brief introduction to them. One of them is used to generate the Insert process and the other is used to generate the Update process.
Sp_GenInsert
After this procedure is run, it generates a complete Insert procedure for the given table. If the original table has an identity column, you have to manually delete the SET IDNTITY_INSERT ON statement in the generated process.
The syntax is as follows
sp_GenInsert ,
Take the northwind database as an example
sp_GenInsert 'Employees', 'INS_Employees'
Finally Generate an Insert stored procedure. Using it, you can develop further.
Sp_GenUpdate
It will generate update stored procedure for a table. The syntax is as follows:
sp_GenUpdate ,,
Take the northwind database as an example
sp_GenUpdate 'Employees','EmployeeID','UPD_Employees '
After running, the stored procedure as shown below is generated:
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 above two stored procedures saves me no less time. Especially in the process of reconstructing each stored procedure after changing the table structure. You can rewrite these two procedures to automatically generate other stored procedures.