SQL Server存储过程Return、output参数及使用技巧

1、不带输入参数的简单存储过程

if object_id('up_user') is not null

drop proc up_user

go

create proc up_user

as

set nocount on

delcare @name varchar(10)

begin

select @name=uname from user

end

set nocount off

go

esec up_user

 

2、带输入参数的简单存储过程

 if object_id('up_user') is not null

drop proc up_user

go

create proc up_user

@id int

as

set nocount on

delcare @name varchar(10)

begin

select @name=uname from user where  uid=@id

end

set nocount off

go

--执行该存储过程

esec up_user 1

 

3、带Return参数的存储过程

 if object_id('up_user') is not null

drop proc up_user

go

create proc up_user

as

set nocount on

delcare @age int

begin

select @age=uage from user

return @age

end

set nocount off

go

 

--执行该存储过程

declare @age int

exec @age=up_user

select @age

 

4、带output参数的存储过程

 if object_id('up_user') is not null

drop proc up_user

go

create proc up_user

@id int,

@name varchar(10) ='' output

as

set nocount on

begin

select @name=uname from user where uid=@id

end

set nocount off

go

 

--执行该存储过程

declare @name varchar(10)

exec up_user 2, @name output

select @name

 

5、同时带Return和output参数的存储过程

if exists(select name from sysobjects where name='up_user' and type='p')

drop proc up_user

go

create proc up_user

@id int,

@name varchar(20) output

as

declare @age int

begin

select @age=stuage,@name=stuname from stuinfo where uid=@id

return @age

end

 

--执行该存储过程

declare @age int

declare @name varchar(20)

exec @age=up_user 2,@name output

select @age,@name