Stored Procedure Always Implicitly Casting Return Value as INT

Question

Stored Procedure Always Implicitly Casting Return Value as INT

Robin Wilson

SSCertifiable

Points: 5017
More actions
September 15, 2015 at 5:54 am

#147527

Hello
I have written a stored procedure which returns a VARCHAR value but for some reason SQL Server always converts it to INT which then fails and I cannot work out how to stop it doing this.
I have tried cast and convert commands in various places but nothing will work.
Here is a simplified version of the function to demonstrate the issue:
CREATE PROCEDURE SPR_Test
AS
BEGIN
SET NOCOUNT ON;
RETURN 'yes'
END
GO
But then if I call it I get this error:
Conversion failed when converting the varchar value 'yes' to data type int.
Please can someone suggest what I might be doing wrong?
Thanks
Robin

Viewing 3 posts - 1 through 2 (of 2 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

https://msdn.microsoft.com/en-us/library/ms187926.aspx

Stored procedures are similar to procedures in other programming languages in that they can:
Accept input parameters and return multiple values in the form of output parameters to the calling procedure or batch.
Contain programming statements that perform operations in the database, including calling other procedures.
Return a status value to a calling procedure or batch to indicate success or failure (and the reason for failure).

The RETURN statement within a procedure is intended to return a numeric status code indicating success or failure, nothing else. You may be looking for an output parameter, which can be of whatever data type you desire and which you may have multiple of.

CREATE PROCEDURE Test (@YesNo VARCHAR(5) OUTPUT)

AS

BEGIN

SET NOCOUNT ON;

SET@YesNo = 'yes'

END

GO

DECLARE @Result VARCHAR(5)

EXEC Test @YesNo = @Result OUTPUT

SELECT @Result

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Robin Wilson SSCertifiable Points: 5017 More actions · Answer 2

Hello Gail

Thanks a lot for your quick response.

Using the OUTPUT value has worked and allowed me to return the value I needed.

Thanks

Robin