从varchar到int的SQL Proc“转换失败".为什么要转换?

问题描述

我的问题是...为什么将其从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“转换失败".为什么要转换?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！