Menu
If a stored procedure returns a value of zero, does that always mean it was run successfully? I am using MS SQL Server 2008.
378
The RETURN statement is used to unconditionally and immediately terminate an SQL procedure by returning the flow of control to the caller of the stored procedure. It is mandatory that when the RETURN statement is executed that it return an integer value. If the return value is not provided, the default is 0.
Return Value in SQL Server Stored Procedure The 0 value indicates, the procedure is completed successfully and the non-zero values indicate an error.
A stored function can return only one value, unlike a stored procedure, which can return multiple values or an entire result set.
If you try to return NULL from a stored procedure using the RETURN keyword, you will get a warning, and 0 is returned instead. If a procedure hits an error that requires it to terminate immediately, it will return NULL because it never gets to either the RETURN keyword or the end of the batch!
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
193
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?
43
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
