文档介绍：66:创建无参数无返回值的存储过程(存储过程的默认返回值都是int类型的,并且为0)
create proc mya
as
select * from orders
go
exec mya
67:删除存储过程
Drop proc 存储过程名称,如果多个用,号进行分割。
68:创建有参数但无返回值的存储过程
create proc myb
***@param int = 1
as
select * from orders where oid > ***@param
go
exec myb 3
69:调用以上俩个存储过程,并查看默认返回值
declare ***@number int
set ***@number = 1;
exec ***@number = mya
exec ***@number = myb 3
print ***@number
go
70:获取存储过程的返回值(存储过程默认的返回值都为0)
declare ***@number int
set ***@number = 1
exec ***@number = mya
exec ***@number = myb
print ***@number
71:创建有参数并且有返回值的存储过程,并调用存储过程,获取返回值
select * from person;
go
--create proc myc
--***@inparm int,
--***@outparm int output
--as
--select ***@outparm=count(*) from person where pid>***@inparm
--go
declare ***@flag int
set ***@flag = 2
exec myc ***@outparm = ***@flag output,***@inparm = 1
if(***@flag>0)
begin
print ***@flag
end
else
begin
print 'set is null'
end
go
--1 调用方法:
--1:不要返回值return,的俩种写法
--2:计算参数总共有多少个输出参数,有多少个输出参数,就需要有多少个变量,output不能少
--创建存储过程时,注意,sql中引用表必须在当前数据库中存在,否则调用不成功
--存储过程与use 间必须用批处理命令 go 去隔开
72:手工改变存储过程的返回值
create proc mya
as
select * from person
return 888
go
declare ***@flag int
set ***@flag = 0
exec ***@flag = mya
print ***@flag
create proc mya
as
declare ***@flag int
set ***@flag = 0
select ***@flag = count(*) from person
return ***@flag
go
declare ***@number int
set ***@number = 0
exec ***@number = mya
print ***@number
73:存储过程综合应用:
create proc mya
***@inparm int,
***@outparm int output