一、创建
1、简单创建
语法:
creat procedure procedure_name[;number]
[{@parameter data_type}
[varying][=default][output]
][,...n]
[with
{recompile|encryption|recompile,encryption}]
[for replication]
as sql_statement[...n]
示例:
use MR_SQL
go
creat procedure pro_cxo_RTB as
select *from MR_RYB where 性别='女'
go
创建存储过程组
use MR_SQL
go
creat procedure PRO_Group;1 as
select * from MR_RYB
go
creat procedure PRO_Group;2 as
select * from MR_RYB where 性别='男'
go
创建局部临时存储过程
creat procedure#PRO_CXO_RYB as
select * from MR_RYB where 性别=‘女’
创建全局临时存储过程,在存储名称前加上##
2、带参数的存储过程
(1)不带参数默认值
use MR_SQL
go
creat procedure PRO_Student_IN
@chinese int,
@English int,
@math int,@class varchar(20)
as
select *
from MR_Student
where 语文>@chinese and 英语> @English and 数学> @math and 班级=@class
go
exec PRO_Student_IN 65,77,70,“三年级一班”
(2)带参数默认值
@chinese int=60,
@English int=60,
@math int=60,
@class varchar(20)='三年二班'
3、带返回值
(1)print语句
语法:
print 'any ASCII text'|@local_variable|@@function|string_expr
示例:
creat procedure PRO_Print
as if exist(select * from MR_Student where 班级='三年一班'
print 'MR_RYB 数据表中的信息‘
else
print'MR_RYB数据表中无信息'
go
(2)用raiserroe函数返回错误信息
示例:
use MR_SQL
go
creat procedure PRO_raiseerror
@errormessage varchar(10)
as
set @errormessage=@errormessage+'单号不存在’
raiserror(@errormessage,16,1)
go
exec PRO_raiserror'1003'
二、查看
sp_helptext
sp_depends
sp_help
三、修改
(1)同创建的语法一样,只是把creat改为alter
(2)重新编译
示例:在执行时进行
exec PRO_Group_name with recomplite
也可以在创建的时候使用
creat procedure PRO_anew
with recomplite
......
四、删除
drop procedure procedure_name