Stored procedure return value

Question

Post reply

Stored procedure return value

sqlfriend

SSC Guru

Points: 52781
More actions
October 5, 2012 at 10:08 am

#262634

Anyone knows in a stored procedure that has code like below:
....
set @TranCount = @@trancount
if not exists(select * from dbo.Product where ProductionID= @ProductionID and LocationID = LocationID)
return 2
begin try
if @TranCount = 0
begin transaction
update products...
where
ProductionID= @ProductionID and LocationID = LocationID
if @TranCount = 0 and @@trancount > 0
commit transaction
return 0
end try
...
What does return 2 mean?
I don't see there is an output parameter in the stored procedure.
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Roland Alexander STL SSCrazy Points: 2915 More actions · Answer 1

The RETURN function can return a value to the caller, but it is up to the caller to capture and process the value:

EXEC @return = dbo.my_procedure;

In this case, (2) is a value chosen by the developer to indicate that nothing was returned by the existence check.

Roland Alexander
The Monday Morning DBA
There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer

Roland Alexander STL SSCrazy Points: 2915 More actions · Answer 2

Roland Alexander STL (10/5/2012)
The RETURN function can return a value to the caller, but it is up to the caller to capture and process the value:
EXEC @return = dbo.my_procedure;
In this case, (2) is a value chosen by the developer to indicate that nothing was returned by the existence check.

Forgot to add that executing the RETURN function ends the processing at that point, and exits the procedure.

Roland Alexander
The Monday Morning DBA
There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

That is known as "return value". It is of INT type and by default stored proc returns 0 (zero).

If you want to read it from within T-SQL you can use something like:

DECLARE @result INT

EXEC @result = dbo.usp_ProcName ....

If you use your proc from .Net application (or other) using ADO, you can get this value using parameter Direction type of adParamReturnValue.

Looks like developer wanted to use return value as some kind of state...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]