What is use of varchar output parameter in stored procedure - cannot 'return @myvarchar' but can 'select @myvarchar'

Question

xample:

CREATE PROCEDURE dbo.sp_Delete (@p1 INT, @p2 VARCHAR(10) OUTPUT)
AS
    --@p1 will have calculations done and get a value for @p2
    SET @p2 = 'test'
    RETURN @p2

Test:

DECLARE @p2 VARCHAR(100)
EXEC sp_Delete
     @p1 = N'1',
     @p2 = N''

Error:

Conversion failed when converting the varchar value 'test' to data type int.

BUT you can do this:

ALTER PROCEDURE dbo.sp_Delete (@p1 INT)
AS
    --@p1 will have calculations done and get a value for @p2
    SELECT 'test'


    EXEC sp_Delete
         @p1 = N'1'

So my question is, what is the use of having a OUTPUT parameter of type varchar (note I'm not questioning an output parameter of type int), when you can't use "return @myvar" if it is a varchar. You can just "select @myvar". I am trying to figure out what I am doing wrong, because I think I don't understand the use of a specific varchar output variable.

Answer 1

An output variable is different from a return value. The return value is always an integer. You can retrieve it like:

exec @retval = dbo.MyStoredProcedure

While an output parameter is retrieved like:

exec dbo.MyStoredProcedure @par_out output

In your case, omit the return statement. set @par_out = 'value' is enough to return the output parameter to the calling code.