views:

36

answers:

4

Hello,

I'm writing a stored procedure. This procedure has a case where if it is met, I want to stop executing the procedure and return -1. How do I do this? Currently, I'm trying the following:

IF @result <> 1
BEGIN
  SELECT -1                 
END

However, SELECT is not a typical "return". As you can imagine I spend most of my time in code which is why i'm looking for something like a "return".

Thank you,

+1  A: 
IF @result <> 1
BEGIN
  SELECT -1          
  RETURN       
END

does not work?

Axarydax
+1  A: 
IF @result <> 1
BEGIN
  RETURN -1                 
END
SELECT * FROM bla
RETURN 0
edosoft
+1  A: 

Try this

IF @result <> 1
BEGIN
  RETURN -1                 
END
Peter Mourfield
A: 

RETURN (Transact-SQL)

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 that follow RETURN are not executed.

When used with a stored procedure, RETURN cannot return a null value. If a procedure tries to return a null value (for example, using RETURN @status when @status is NULL), a warning message is generated and a value of 0 is returned

.

It is a common convention to return zero 0 when everything is fine, a negative value when there is a soft error (like validation or user warning) and a positive value for a hard error, like insert failed, etc.

...

IF @result!=1
BEGIN
  RETURN -1 --there was an error!          
END

....

RETURN 0  --everything is fine
KM