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
