SQL Proc 'Conversion failed' from varchar to int. Why the conversion?

Question

My question here would be... Why is it converting to int from varchar? I'm not sure what it is trying to do

CREATE PROCEDURE #myTestProcedure 
(
    @TransId VARCHAR(15)
) 
AS
BEGIN
    DECLARE @Result VARCHAR(15);

WITH TestCTE (TransId, AdjRefTransId) AS
(
    SELECT TRANSID, ADJREFTRANSID
    FROM dbo.MyTable
    WHERE TRANSID = @TransId

    UNION ALL

    SELECT pet.TRANSID, pet.ADJREFTRANSID
    FROM dbo.MyTable AS pet
    JOIN TestCTE
        ON TestCTE.ADJREFTRANSID = pet.TRANSID
)

SELECT @Result = 
(
    SELECT MAX(MyResult)
    FROM dbo.MyOtherTable
    WHERE TRANSID = TestCTE.TRANSID
)        
FROM TestCTE
WHERE TestCTE.ADJREFTRANSID = ''

RETURN @Result
END

EXEC dbo.#myTestProcedure @TransId = 'MyTransId'

Error:

Msg 245, Level 16, State 1, Procedure #myTestProcedure  0004C61A, Line 32
Conversion failed when converting the varchar value 'MyResult' to data type int.

I can't see where it is trying to make this conversion. Line 32 is a blank line. No code there.

Interstice

The documentation spells it out: RETURN [ integer_expression ] <--- this can't be a string and, as Sean says, should not be used to output data. It should only be used for status. – Evolve

Answer 1

It is your RETURN. Stored procedures return an integer to indicate the status of the execution, not return values. You would either need to Select @Result OR have @Result be an output parameter.

Gravante