CODEWITHSUNDEEP
sqlsql-servert-sql
Ching Liu
Ching Liu

Reputation: 1656

Stored procedure return conversion error when there should be no conversion

I have a stored procedure that just returns a varchar(1000), but when I run using the following block:

declare @P1 VARCHAR(1000) 
SET @P1='' 
PRINT 'P1='+@P1
exec Test @PhoneNumber='1234567890',@AgentID=N'Test AgentID',@SP_RETURN=@P1 output  
PRINT 'P1='+@P1

I get

Msg 50000, Level 16, State 1, Procedure Test, Line 50
Conversion failed when converting the varchar value 'Complete' to data type int..

Msg 245, Level 16, State 1, Procedure Test, Line 60
Conversion failed when converting the varchar value 'Exception raised' to data type int.

This is the stored procedure:

Create PROCEDURE [dbo].[Test]  
    @PhoneNumber as CHAR(10),  
    @AgentID as VARCHAR(100),  
    @SP_RETURN as VARCHAR(1000) OUTPUT,  
    @UTCDateTimeOverride as Datetime = NULL  
AS  
BEGIN  
 BEGIN TRY  
  SET @SP_RETURN = N'Complete'
  RETURN @SP_RETURN  
 END TRY  

 BEGIN CATCH  
  SET @SP_RETURN = N'Exception raised'
  RETURN @SP_RETURN  
 END CATCH  
END  
go

Any suggestions?

Upvotes: 1

Views: 2049

Answers (4)

MiroslavStojakovic
MiroslavStojakovic

Reputation: 167

It is good practice to use return in stored procedures just to give feedback is procedure executed successfully or not. To return values it is better it is better to use output parameters.

Using output parameters you are able to: 1.return more then 1 one value (using return you are able to return just one value); 2.return any data type (with return keyword you can return only int).

Upvotes: 0

jradich1234
jradich1234

Reputation: 1425

You're already specifying @SP_RETURN as OUTPUT. You don't need it in your RETURN statements as well. Just "RETURN"

Upvotes: 0

M.Ali
M.Ali

Reputation: 69514

A stored Procedure can only return an Int value. Varchar values are "returned" via OUTPUT variables. 

Create PROCEDURE [dbo].[Test]  
 @PhoneNumber as CHAR(10),  
 @AgentID as VARCHAR(100),  
 @SP_RETURN as VARCHAR(1000) OUTPUT,  
 @UTCDateTimeOverride as Datetime = NULL  
AS  
BEGIN  
 BEGIN TRY  
  SET @SP_RETURN = N'Complete'
  --RETURN @SP_RETURN  not needed
 END TRY  

 BEGIN CATCH  
  SET @SP_RETURN = N'Exception raised'
  -- no need for return statement here 
 END CATCH  
END  
go

Now when executing the proc you will do something like ...

DECLARE @RETURN_Value VARCHAR(1000);

Exec [dbo].[Test]   @PhoneNumber = 'Some Value'  
                   ,@AgentID     = 'Some Value'
                   ,@SP_RETURN   = @RETURN_Value OUTPUT

Now the @RETURN_Value variable will have the values

Upvotes: 1

radar
radar

Reputation: 13425

stored procedure always return integer, which is used to return the error code

you are returning a varchar field causing the exception

The error is caused by this

RETURN @SP_RETURN

do this instead

  BEGIN TRY  
  SET @SP_RETURN = N'Complete'
  RETURN 0  -- success
  END TRY  

  BEGIN CATCH  
  SET @SP_RETURN = N'Exception raised'
  RETURN  ERROR_NUMBER()  -- error code

Upvotes: 5

Related Questions