<!--
参数详解:http://hi.baidu.com/pigbones/blog/item/9b63ec005d1c8a19738b65e1.html

1.最普通的sp
create procedure sp1 as select * from userinfo
  命令行执行:exec sp1

带输入参数的sp
create procedure sp2 @username varchar(10) as select username,datetime from userinfo where username=@username
  命令行执行:exec sp2 @username='test' 或exec sp2 test

2.带输入输出参数的sp
create  PROCEDURE sp3
@Username varchar(10),
@UserID int output,
@age int output
as
set nocount on
begin
 if @Username is null return
 select @Userid=id,@age=age
 from dbo.[userinfo]
 where username=@username
 return
end

 命令行执行:declare @userid int,@age int
 exec sp3 'test',@userid output,@age output
 select @userid as 编号,@age as 年龄

3.带输出参数的sp
create procedure sp4
@username varchar(10) output
as
select @username=username from userinfo where username='test'

 命令行执行:declare @username varchar(10)
 exec sp4 @username output
 select @username

4.输入输出存储过程实例

CREATE PROCEDURE login
@infullname nvarchar(50),
@inpassword nvarchar(50),
@outcheck char(3) output
as
if exists(select * from userinfo where username=@infullname and [password]=@inpassword)
select @outcheck='yes'
else
select @outcheck='no'
GO

数据库 victor 表 admin 字段 username password

5.输入参数数字
create procedure sp2_2 @id varchar(10) as select id, username,datetime from userinfo where id=@id
exec sp2_2 @id=3

6.
create procedure test3
@username varchar(20),@age int
as
insert into userinfo(username,age) values(@username,@age)

exec test3 '名称',25

7.
create procedure test5 @str varchar(10) output
as
begin
if exists(select username from userinfo where username='test')
set @str="yeah"
else
set @str="no"
end

declare @str varchar(10)
exec test5 @str output
select @str

8.
CREATE procedure complex @username varchar(10),@password varchar(10),@str varchar(50) output
as
begin
if exists(select * from userinfo where username=@username and password=@password)
set @str="登录成功"
else
set @str="用户名密码错误"
end
GO

declare @str varchar(50)
exec complex 'test',飋siyu1',@str output
select @str

测试练习:

create procedure test1 as print 'hello the world!'
exec test1

create procedure test2 as select top 3 id,username,datetime from userinfo
exec test2

create procedure test3 @username varchar(10) as select username,age from userinfo where username=@username
declare @username varchar(10)
set @username='test'
exec  test3 @username

create procedure test4 @age int output as select @age=age from userinfo order datetime desc
declare @age int
exec test4 @age output
select @age

create procedure test5 @username varchar(10),@string varchar(50) output
as
begin
if exists(select * from userinfo where username=@username)
set @string='存在'
else
set @string='该用户不存在'
end
declare @username varchar(10),@string varchar(50)
set @username='yan'
exec test5 @username,@string output
select @string
-->