469,568 Members | 1,674 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,568 developers. It's quick & easy.

RETURN statement odd behavior?

Consider the following (questionable, to be sure, but syntactally legal) stored
procedure (using the Northwind database):

Create Procedure Test
As
Return (Select Count(*) From Orders)
Select * From Orders;

If you execute this SP in QA via:

Declare @test int;
exec @test = test;
print 'Order count = ' + cast(@test as varchar);

you'll notice that you get a correct return value, but you also get the entire
resultset from the Orders table(!), this despite the fact that BOL states:

"The RETURN statement unconditionally terminates a query, stored procedure, or batch.
None of the statements in a stored procedure or batch following the RETURN statement
are executed."

If you change the procedure definition to:

Create Procedure Test
As
Declare @tmp int;
Select @tmp = Count(*) From Orders;
Return @tmp;

Select * From Orders;

Then things behave as expected, returning only the return value.

Worse, if the procedure is defined as:

Create Procedure Test
As
Return (Select Count(*) From Orders);
Return -1;

This will return a -1 regardless of how many rows are in the Orders table.

Anybody know what's going on here?

- Jeff
Jul 20 '05 #1
0 1561

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Noah Roberts | last post: by
173 posts views Thread by Marty James | last post: by
34 posts views Thread by Davy | last post: by
49 posts views Thread by Davy | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.