SQLSERVER 2000 存储过程例子

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