create
table
test(id
int
primary
key
,name
char
(
10
))
insert into test values ( 1 , ' test1 ' )
insert into test values ( 2 , ' test2 ' )
insert into test values ( 3 , ' test3 ' )
insert into test values ( 4 , ' test4 ' )
1 、返回结果集
create procedure return_result
as
select * from test
exec return_result
-- 调用
-- id name
-- 1 test1
-- 2 test2
-- 3 test3
-- 4 test4
2 、输入参数和输出参数
create procedure input_output
@id int ,
@name char ( 10 ) output
as
begin
select @name = name from test where id = @id
end
-- 调用
declare @name char ( 10 )
exec input_output 1 , @name output
select @name name
-- 输出结果
-- name
-- test1
3 、返回值
-- 每个存储过程都可以以一个return语句返回一个整型(integer)的值
alter procedure return_value
@id int
as
begin
declare @count int -- 只能返回整型的值
select @count = count ( * ) from test where id = @id
return @count
end
-- 调用
declare @count char ( 10 )
exec @count = return_value 1
select @count shuliang
-- 结构
-- shuliang
-- 1
insert into test values ( 1 , ' test1 ' )
insert into test values ( 2 , ' test2 ' )
insert into test values ( 3 , ' test3 ' )
insert into test values ( 4 , ' test4 ' )
1 、返回结果集
create procedure return_result
as
select * from test
exec return_result
-- 调用
-- id name
-- 1 test1
-- 2 test2
-- 3 test3
-- 4 test4
2 、输入参数和输出参数
create procedure input_output
@id int ,
@name char ( 10 ) output
as
begin
select @name = name from test where id = @id
end
-- 调用
declare @name char ( 10 )
exec input_output 1 , @name output
select @name name
-- 输出结果
-- name
-- test1
3 、返回值
-- 每个存储过程都可以以一个return语句返回一个整型(integer)的值
alter procedure return_value
@id int
as
begin
declare @count int -- 只能返回整型的值
select @count = count ( * ) from test where id = @id
return @count
end
-- 调用
declare @count char ( 10 )
exec @count = return_value 1
select @count shuliang
-- 结构
-- shuliang
-- 1