Cannot set return value in variable in SQL Proc

Question

Hi, I created a simple stored proc which returns a string

create proc im.mmtest as 
select 'testvalue'
go

then I call the SP and set its return value to a variable. This block is executed by selecting all three lines and pressing F5. I do see the value when the exec statement is executed, but I do not get the string value back in select @ret_var.

Please help me understand why. I am using this in a complicated SP and don't know why the value from the SP is not displayed correctly.

declare @ret_var as varchar(10)
exec @ret_var =  im.mmtest
select @ret_var

Thank you in advance for your help.

Answer 1

create proc im.mmtest as 
select 'testvalue'
RETURN 99 --insert value here
go

Return value is set by RETURN and is always integer

SELECT like this will return a record set. here, one column and one row

Perhaps you want an output parameter

create proc im.mmtest
  @foo varchar(100) OUTPUT
as 
select @foo = 'testvalue'
go

DECLARE @bar varchar(100)
EXEC im.mmtest @bar OUTPUT
SELECT @bar

Answer 2

You're not returning a return value (which you'd have to do using the RETURN statement which only supports integers anyway), but a resultset. Try an output parameter instead:

create proc im.mmtest 
    @OutVal VARCHAR(10) OUTPUT
as 
select @OutVal = 'testvalue' 
go

and then call it like this:

DECLARE @Val VARCHAR(10)
EXECUTE im.mmtest @Val OUTPUT
SELECT @Val

The syntax for a return value (which MUST be numeric) is:

RETURN(1) -- RETURN value 1

The syntax you are currently using would receive that return value. But you can't do it with anything non-numeric, hence you'd be best off with an OUTPUT parameter.

Answer 3

Return the varchar value in an output parameter of the proc.

CREATE PROC im.mmtest (@retVal varchar(10) OUTPUT) AS
  SELECT @retVal = 'testvalue'
GO

... then get the value from the SP thusly:

DECLARE @ret_var varchar(10)
EXEC im.mmtest(@ret_var)
SELECT @ret_var