Definition, modification, and deletion of stored procedures in SQL, and SQL stored procedures

Definition, modification, and deletion of stored procedures in SQL, and SQL stored procedures

1. Storage Process Classification

• System stored procedures
• Local Stored Procedure (User-Defined)
• Temporary stored procedures (local [#] and global [#] temporary stored procedures)

2. Create a stored procedure

-- Create procedure sp_goods_price @ minprice float, @ maxprice floatas select * from goods where price >=@ minprice and price <= @ maxpricego

Run the stored procedure: execute sp_goods_price 200 2000

3. Modify the Stored Procedure

create procedure sp_goods_betw@minprice float =200,@maxprice float=3000as select * from goods where price>=@minprice and price <=@maxpricego 

4. delete a stored procedure

drop procedure sp_goods_price

5. View stored procedures

sp_helptext procedureNamesp_help procedureName

6. Rename the Stored Procedure

exec sp_rename oldName newName 

** Local stored procedures

create procedure #sp_goods_betw@minprice float ,@maxprice floatas select * from goods where price>=@minprice and price <=@maxpricego

** Global Stored Procedure

create procedure ##sp_goods_betw@minprice float ,@maxprice floatas select * from goods where price>=@minprice and price <=@maxpricego

** Stored procedure without caching

create procedure sp_goods_betw@minprice float ,@maxprice floatwith recompileas select * from goods where price>=@minprice and price <=@maxpricego

** Encryption Stored Procedure

create procedure sp_goods_betw@minprice float ,@maxprice floatwith enctyptionas select * from goods where price>=@minprice and price <=@maxpricego

Summary

The above section describes the definition, modification, and deletion of stored procedures in SQL. I hope it will help you. If you have any questions, please leave a message for me, the editor will reply to you in a timely manner. Thank you very much for your support for the help House website!