How to define, modify and delete stored procedures

1. Classification of stored procedures

• System stored procedures

• Local stored procedures (user-defined)

• Temporary stored procedures (local [#], global [##] temporary stored procedures)

2. Create stored procedures

--选出价格区间的商品信息create procedure sp_goods_price@minprice float ,@maxprice floatas select * from goods 
where price>=@minprice and price <=@maxpricego

Execute 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 the stored procedure

drop procedure sp_goods_price

5. View the stored procedure

sp_helptext procedureName
sp_help procedureName

6. Rename the stored procedure

exec sp_rename oldName newName

**Local stored procedures

<p style="margin-bottom: 7px;">create procedure #sp_goods_betw@minprice float ,@maxprice floatas select * from goods <br/>where price>=@minprice and price <=@maxpricego<br/></p>

**Global stored procedures

##

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

** Uncached stored procedure

   ,

with recompile

as select * from goods 
where price>=@minprice and price <=@maxpricego

**Encrypted stored procedure

   ,

with enctyption

as select * from goods 
where price>=@minprice and price <=@maxpricego

The above is the detailed content of How to define, modify and delete stored procedures. For more information, please follow other related articles on the PHP Chinese website!