Return data with a return code:

     

A procedure can return an integer value, called a return code, to indicate   the state of execution of a procedure. The code of   return for a procedure using the RETURN instruction. As   that with the OUTPUT parameters, you must save the return code in   a variable when the procedure is executed in order to use its value   in the calling program.

For example, when executing the following procedure you will get:

CREATE PROCEDURE PROCEDIMIENTO
    @PARAMETRO VARCHAR(10) OUTPUT 
AS
BEGIN    
    SELECT @PARAMETRO = 'CADENA'
    RETURN 1
END

DECLARE @PARAMETRO VARCHAR(10); 
DECLARE @RESULTADO INT;
EXEC @RESULTADO = PROCEDIMIENTO @PARAMETRO OUTPUT

SELECT @RESULTADO, @PARAMETRO

+---+--------+
| 1 | CADENA |
+---+--------+

But in case you want to return a different value to an integer, it will throw you an error.

For example, modifying:

ALTER PROCEDURE PROCEDIMIENTO
    @PARAMETRO VARCHAR(10) OUTPUT 
AS
BEGIN    
    SELECT @PARAMETRO = 'CADENA'
    RETURN 'ABC'
END

DECLARE @PARAMETRO VARCHAR(10); 
DECLARE @RESULTADO INT;
EXEC @RESULTADO = PROCEDIMIENTO @PARAMETRO OUTPUT

SELECT @RESULTADO, @PARAMETRO

You will get the error:

  

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

Reference:

• Returning data from a procedure Stored