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 >
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.
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.