Procedure RETURNING VALUE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Procedure RETURNING VALUE

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2007-04-25 : 03:16:45

Hi pals,

I came across one doubt. i.e

If we put a return statement within a stored procedure then what does it mean.
Basically a stored procedure never returns a value.But it can be possible using
OUTPUT parameters.But if you take the below piece of code i.e a stored procedure containing a
return stmt (i.e mainly used for Exception handling in SQL Server 2000). What does it mean,

Any comments on this.

create table sample
(id int,
name varchar(20)
)

create procedure usp_sample
@v_id int,
@v_name varchar(20)
as
declare @err int,
begin

begin transaction
insert into sample values(@v_id,@v_name)
set @err=@@ERROR
if(@err!=0)
goto exception_block
select 2/0
if(@err!=0)
goto exception_bl
commit transaction
return 0
exception_block;
rollback transaction
return @err

end

exec usp_sample 1,'ram'

Note : i purposefully added the SELECT 2/0 stmt. to cause DIVIDE BY ZERO error Exception.
-----
But i need to know what is the significance of putting return 0.
I also test the procedure by commenting -- RETURN 0 stmt and tried to execute the
stored procedure. But in this case what is happening is that, instead of rollbacking the
entire transaction the insert stmt is getting commited and exception is also thrown.But i
expected the entire code to be rollbacked. The insert stmt should not get commited if an exception
is raised. So i uncommented RETURN 0 stmt and then again i ran the stored procedure. This
time it is behaving as i was expecting.(i.e the entire txn is getting rollbacked).

But my doubt is a procedure can never return a value as of my knowlegde because
a function can return 1 value.

I need some sincere comments on this .

With Regards,
frank

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-04-25 : 08:31:38

quote:
Basically a stored procedure never returns a value.

Since when? Books Online is your best friend(I know it's my best friend). It answers your question in full.

RETURN
Exits unconditionally from a query or procedure. RETURN is immediate and complete and can be used at any 
point to exit from a procedure, batch, or statement block. Statements following RETURN are not executed.
Return Types
Optionally returns int.

Note  Unless documented otherwise, all system stored procedures return a value of 0
, which indicates success; a nonzero value indicates failure.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-25 : 08:38:46

The basic mistake you made while coding your procedure is the usual mistake a SQL newcomer makes i.e. not checking @@Error status after each statement.

In your case, you do check status of your custom variable @error. But you forgot to reset it to the value of @@error after "SELECT 2/0":

create procedure usp_sample
@v_id int,
@v_name varchar(20)
as
declare @err int,
begin

begin transaction
insert into sample values(@v_id,@v_name)
set @err=@@ERROR
if(@err!=0)
goto exception_block
select 2/0
set @err=@@ERROR
if(@err!=0)
goto exception_bl
commit transaction
return 0
exception_block;
rollback transaction
return @err

end

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2007-04-26 : 04:55:00

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources