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.

Answer 2

RETURN can return only int values. Your don't need to use RETURN, actually, just omit it:

 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'

...

declare @p1 int, @p2 varchar(100)
SELECT @p2 = '', @p1 = 1
exec sp_Delete
@p1,
@p2 OUTPUT

select @p2

Answer 3

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

Answer 4

You can use set

declare
 @variable_name datatype
begin
 set @variable_name= (select query)
end

Don't use a return statement to return a varchar value