Undocumented Return Trick?

Question

Undocumented Return Trick?

Mark_maddison

Right there with Babe

Points: 768
More actions
July 29, 2003 at 9:03 am

#82926

We discovered a Stored Procedure breaking the laws of SQL Server.
--------------------------------
return (select count(*)
from tWebPage W1
inner join tWebPage W2 on upper(rtrim(ltrim(W1.Link)))=upper(rtrim(ltrim(W2.Link)))
where W1.ID=@ID)
select * from tWebPage where ID = @ID
--------------------------------
SQL states that when a stored proc hits a Return statement all statements after that will NOT execute.
This DOES execute the 2nd statement.
IT is returning the appropriate count and returning the recordset, but it is not supposed to.
Insight?

Viewing 13 posts - 1 through 12 (of 12 total)

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

SQLBill SSC Guru Points: 51440 More actions · Answer 1

Where did you get the information that a RETURN in a stored procedure will stop all other SELECTs?

I looked in BOL, used the Index tab, entered Stored Procedure and selected Overview. There's an example of a stored procedure with a RETURN including a SELECT statement.

-SQLBill

paul_richardson SSCarpal Tunnel Points: 4594 More actions · Answer 2

if you look under return in BOL it says the following :-

"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.

When used in a stored procedure, the RETURN statement can specify an integer value to return to the calling application, batch, or procedure. If no value is specified on RETURN, a stored procedure returns the value 0.

Most stored procedures follow the convention of using the return code to indicate the success or failure of the stored procedure. The stored procedures return a value of 0 when no errors were encountered. Any nonzero value indicates an error occurred."

Paul

Mark_maddison Right there with Babe Points: 768 More actions · Answer 3

<b>RETURN</b>

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.

Is it possible I am not looking at Return correctly?

Thank You

Mark_maddison Right there with Babe Points: 768 More actions · Answer 4

I guess I got hung up on the

Statements following RETURN are not executed.

But statements are executed after?

SQLBill SSC Guru Points: 51440 More actions · Answer 5

This is interesting. I think it's a mistake in BOL.

I looked at your reference and found the same thing you did.

But now go to BOL, use the Index tab, enter Stored Procedure. Double click on Overview, select SQL Stored Procedures - SQL Server Architecture.

Scroll down to the examples. The example uses a RESULT with a SELECT statement.

So I would say it's not undocumented. It's just bad or conflicting documentation.

-SQLBill

Mark_maddison Right there with Babe Points: 768 More actions · Answer 6

I have done some follow tests with this sp and found the following:

Both Return and Recset return if return is using a 'select count(*)...'

The Recset does not return if returning 'return (select 99)' or 'return 99'. It does indeed stop processing the statement after the return.

Undocumented Feature?

dvernet SSC Rookie Points: 28 More actions · Answer 7

Microsoft® SQL Server™ 2000 stored procedures return data in four ways:

A result set for each SELECT statement contained in the stored procedure or any other stored procedures called by the stored procedure.

......

versus

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.

......

Horrible documentation - imagine explaining THAT to your developers. Something akin to 'yeah - just ignore the documentation when it's wrong'.

Mark_maddison Right there with Babe Points: 768 More actions · Answer 8

I figured it was ust something along those lines.

Thank you

David Wyche Newbie Points: 6 More actions · Answer 9

personally i think it's a bug... if you strip the sp code down to:

Return (Select count(*) From MyTable);

Return -1;

you get -1 for the return value...

Arti SSC Veteran Points: 215 More actions · Answer 10

I too think it is a bug but it can be used in creative way.

If you write:

Return (Select count(*) From MyTable);

select * from MyTable

your procedure returns record count from MyTable - one can assign return value somewhere in the procedure and do some processing after.

Of course if you add

return -1

later on, the return value will be overwritten and you will get -1.

Anyway, just to be on the safe side I would not use return with select.

Artur (starting member 😉

lawrencek SSC-Addicted Points: 444 More actions · Answer 11

I am going with bug has anyone reported it yet?

the following sp

Proc ==================

CREATE PROCEDURE dbo.StoredProcedure1

AS

Print 'Here 1'

RETURN (Select Count(*) from INFORMATION_SCHEMA.COLUMNS)

Print 'Here 2'

RETURN (Select Count(*) from INFORMATION_SCHEMA.TABLES )

Print 'Here 3'

RETURN -1

Print 'Here 4'

QA=====================

Produces this when run in QA with

DECLARE @rc int

EXEC @rc = StoredProcedure1

Select @rc

Output ====================

Here 1

Here 2

Here 3

-----------

-1

(1 row(s) affected)

Mark_maddison Right there with Babe Points: 768 More actions · Answer 12

Mark_maddison

Right there with Babe

Points: 768

August 5, 2003 at 8:17 am

#468665

Thank you all for the posts