从varchar到int的SQL Proc“转换失败".为什么要转换? [英] SQL Proc 'Conversion failed' from varchar to int. Why the conversion?

问题描述

我的问题是...为什么将其从varchar转换为int?我不确定它要做什么

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'

错误:

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

我看不到它在尝试进行此转换的位置.第32行是空白行.那里没有代码.

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

推荐答案

这是您的RETURN.存储过程返回一个整数来指示执行状态，而不返回值.您可能需要选择@Result或将@Result作为输出参数.

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.

这篇关于从varchar到int的SQL Proc“转换失败".为什么要转换?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！