<!--
参数详解: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
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
-->
