February 10, 2014 at 6:42 am
Use [myDatabase]
Create procedure [dbo].[TestReturn]
(
@Result varchar(50) output
)
IF object_id('tempdb..#Temp_Table') IS NOT NULL
BEGIN
SET @Result = 'Exists'
Return
END
ELSE
BEGIN
SET @Result = 'Not Exists'
Return
END
SET @ReturnValue = 'At Last'
If I create the procedure above and call it:
Declare @SPReturn varchar(50)
EXEC TestReturn @SPReturn output
SELECT @SPReturn
@SPReturn has been set to 'Not Exists'. I.e. the procedure determined the Temp Table did not exist, set @Result to 'Not Exists' and exited.
If, instead of writing
SET @Result = 'Not Exists'
Return
I write
SET @Result = 'Not Exists'
Return @Result
An error is reported - can't convert 'Not Exists' to an int.
Why is a conversion to int being attempted? I have created @Result as a varchar(50). If I just 'Return', it works okay, If I 'Return @Result' it reports that error. Don't understand.
February 10, 2014 at 6:47 am
The return value of a stored procedure is int, and cannot be changed. Traditionally it was used to indicate success (RETURN 0) or failure (RETURN n, usually negative).
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 10, 2014 at 6:50 am
ChrisM@Work (2/10/2014)
The return value of a stored procedure is int, and cannot be changed. Traditionally it was used to indicate success (RETURN 0) or failure (RETURN n, usually negative).
So, what is happening in the first bit of my code. It seems to be returning 'Not Exists' as the output.
Is there a difference between ...
The value returned in an output parameter (which can be, for example, a varchar)
and ...
The 'return value' of a stored procedure?
February 10, 2014 at 6:57 am
You're also trying to assign a value to the undeclared variable @ReturnValue. I changed your procedure a bit. Does this accomplish what you're after?
Create procedure [dbo].[TestReturn](@Result varchar(50) output)
AS
BEGIN
IF object_id('tempdb..#Temp_Table') IS NOT NULL
BEGIN
SET @Result = 'Exists'
Return
END
ELSE
BEGIN
SET @Result = 'Not Exists'
Return
END
SET @Result = 'At Last'
END;
go
Declare @SPReturn varchar(50)
EXEC TestReturn @SPReturn output
SELECT @SPReturn
Of course, the line where you set @Result to 'At Last' will not be executed because the object_id check will always fire one branch of your IF, both of which will return.
February 10, 2014 at 7:00 am
sku370870 (2/10/2014)
ChrisM@Work (2/10/2014)
The return value of a stored procedure is int, and cannot be changed. Traditionally it was used to indicate success (RETURN 0) or failure (RETURN n, usually negative).So, what is happening in the first bit of my code. It seems to be returning 'Not Exists' as the output.
Is there a difference between ...
The value returned in an output parameter (which can be, for example, a varchar)
and ...
The 'return value' of a stored procedure?
Does this help to illustrate the difference between return parameters and the return value of the procedure?
Declare @SPReturn varchar(50),
@intReturn integer;
EXEC @intReturn = TestReturn @SPReturn output
SELECT @intReturn, @SPReturn
February 10, 2014 at 7:09 am
Sorry, that '@ReturnValue' error was a typo. Caused by the fact that I have probably typed the line
Set @ReturnValue = -999
tens of thousands of times!
February 10, 2014 at 7:11 am
Ed Wagner (2/10/2014)
sku370870 (2/10/2014)
ChrisM@Work (2/10/2014)
The return value of a stored procedure is int, and cannot be changed. Traditionally it was used to indicate success (RETURN 0) or failure (RETURN n, usually negative).So, what is happening in the first bit of my code. It seems to be returning 'Not Exists' as the output.
Is there a difference between ...
The value returned in an output parameter (which can be, for example, a varchar)
and ...
The 'return value' of a stored procedure?
Does this help to illustrate the difference between return parameters and the return value of the procedure?
Declare @SPReturn varchar(50),@intReturn integer;
EXEC @intReturn = TestReturn @SPReturn output
SELECT @intReturn, @SPReturn
What would happen if a Stored Procedure does not define (at least) one of its parameters as output?
Does every stored procedure have a 'return value' even if an output parameter is not declared?
February 10, 2014 at 7:16 am
sku370870 (2/10/2014)
Ed Wagner (2/10/2014)
sku370870 (2/10/2014)
ChrisM@Work (2/10/2014)
The return value of a stored procedure is int, and cannot be changed. Traditionally it was used to indicate success (RETURN 0) or failure (RETURN n, usually negative).So, what is happening in the first bit of my code. It seems to be returning 'Not Exists' as the output.
Is there a difference between ...
The value returned in an output parameter (which can be, for example, a varchar)
and ...
The 'return value' of a stored procedure?
Does this help to illustrate the difference between return parameters and the return value of the procedure?
Declare @SPReturn varchar(50),@intReturn integer;
EXEC @intReturn = TestReturn @SPReturn output
SELECT @intReturn, @SPReturn
What would happen if a Stored Procedure does not define (at least) one of its parameters as output?
Does every stored procedure have a 'return value' even if an output parameter is not declared?
Like Chris said above, it will return an integer. Even if you don't have a RETURN statement, it'll still return an integer to indicate success or failure. You don't need any output parameters in your stored procedures. When a procedure is used to "return" a data set, you simply SELECT it near the end of the procedure.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply