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

You're confusing the two things, RETURN value and OUTPUT parameters.

RETURN is the "overall" status of the sp execution, whereas OUTPUT parameters allow you to send back multiple values calculated in your procedure.

Also, for OUTPUT parameters to send back their value, you should specify the OUTPUT keyword

declare @p2 varchar(100)
exec sp_Delete
@p1=N'1',
@p2=@p2 OUTPUT -- first @p2 is then sp_parameter name, second is the @p2 variable from you outer batch

select @p2

You can mix RETURN values with OUTPUT parameters

CREATE PROCEDURE TestProc
@P1 INT, 
@P2 VARCHAR(10) OUTPUT
AS
BEGIN
   SET @P2='SOME VALUE'
   RETURN @P1
END
GO
DECLARE @R INT, @P VARCHAR(10)
EXEC @R=TestProc @P1=1, @P2=@P OUT --OUT is short for OUTPUT, you can use either one
SELECT @R, @P

-----------------
1, 'SOME VALUE'

Without using the OUTPUT keywourd you'll have

DECLARE @R INT, @P VARCHAR(10)
EXEC @R=TestProc @P1=1, @P2=@P -- notice the missing OUTPUT
SELECT @R, @P --was not assigned in this batch so is NULL

-----------------
1, NULL