16

If a stored procedure returns a value of zero, does that always mean it was run successfully? I am using MS SQL Server 2008.

bobs
  • 21,844
  • 12
  • 67
  • 78
chobo
  • 31,561
  • 38
  • 123
  • 191
  • 1
    Not sure what your question is. If you can write the SP and choose the return value, isn't the answer "no"? – dkretz May 17 '11 at 17:39
  • Related info on the Return statement might help too: http://stackoverflow.com/questions/2578536/msdn-about-stored-procedure-default-return-value – TheEmirOfGroofunkistan Dec 18 '14 at 16:36
  • For more detail about Store Procedure Return, you can read these article on MSDN https://msdn.microsoft.com/en-us/library/ms188655.aspx and https://msdn.microsoft.com/en-us/library/ms174998.aspx – Tahir Alvi Sep 01 '15 at 12:36

3 Answers3

13

No, you can return something yourself

example

CREATE PROC pr_test AS 
SELECT 1/0

RETURN 0
GO

Now run it

DECLARE @i INT
exec @i = pr_test

SELECT @i  -- will be 0

DROP PROC pr_test

Now let's do it again without the return statement

CREATE PROC pr_test2 AS 
SELECT 1/0

GO

DECLARE @i INT
exec @i = pr_test2

SELECT @i  -- will be - 6

Better to use an output parameter to pass back statuses and or messages

SQLMenace
  • 132,095
  • 25
  • 206
  • 225
  • 4
    How is the return value 6? and the result says - Msg 8134, Level 16, State 1, Procedure pr_test2, Line 3 Divide by zero error encountered. (1 row(s) affected) – Mahesh Jun 07 '16 at 14:40
7

An @@ERROR return value of "zero" indicates that your procedure completed without any errors.

Of course, that doesn't mean that it did what you wanted it to...

Can you be more specific about what you're looking at?

Seattle Badger
  • 308
  • 1
  • 2
  • 6
5

You can use the Return word to return any integer value from a Stored Procedure. That means that zero does not mean that the stored procedure was executed successfully.

Akram Shahda
  • 14,655
  • 4
  • 45
  • 65