使用输出传递输入的T-SQL存储过程

Be easy on me... still a newbie T-SQL programmer :)

容易对我……还是一个新手T-SQL程序员:

I have a Stored Procedure that is intended to take three input parameters, evaluate them and send back one parameter (@eligible). If I just execute a T-SQL script with variables hard coded in, my @@ROWCOUNT sets @eligible to 1. When I EXECUTE a call to it as a stored procedure, it does not return @eligible correctly. Here is the procedure:

我有一个存储过程，它打算获取三个输入参数，对它们进行评估，然后返回一个参数(@ qualified)。如果我只是执行一个带有硬编码的变量的T-SQL脚本，那么我的@@ROWCOUNT设置@ qualified为1。当我作为存储过程执行对它的调用时，它不会正确地返回@ valid。这是程序:

ALTER PROCEDURE [dbo].[proc_Eligible]
                (@control AS nvarchar(10),
                 @checkno AS nvarchar(10),
                 @event_type AS nvarchar(7),
                 @eligible AS bit OUTPUT)
AS

BEGIN
    SET @eligible = 0

    SELECT @control AS Control, @checkno AS CheckNum

    -- Is the check drawn on an eligible bank?
SELECT
        H.CONTROL,
        H.NAME,
        H.RECV_DATE,
        H.CHECK_NUM,
        H.BANK,
        SUM(D.RECV_AMOUNT)
FROM
        [ZZZ].[dbo].[MRRECVH] H INNER JOIN
        [ZZZ].[dbo].[MRRECVD] D ON H.control  = D.CONTROL
WHERE
        BANK IN (SELECT
                    RIMAS_Code
                 FROM
                    [Custom].[dbo].[Bank_Account])
    AND H.CONTROL = @control
    AND H.CHECK_NUM = @checkno
GROUP BY
        H.CONTROL,
        H.BANK,
        H.NAME,
        H.CHECK_NUM,
        H.RECV_DATE
HAVING
        SUM(D.RECV_AMOUNT) > 0

    IF @@ROWCOUNT > 0   
    SELECT @eligible = 1
END

(On the next to last line, I have tried 'SET @eligible = 1', but that didn't make any difference).

(在最后一行，我尝试了“SET @ qualified = 1”，但这没有任何区别)。

To call the procedure:

调用过程:

DECLARE
    @eligible AS bit

EXECUTE proc_Eligible
    @Control     = '3034'
    ,@Checkno    = '5011'
    ,@event_type = 'update'
    ,@eligible   = @eligible

SELECT @eligible

As I mentioned, if I isolate the stored procedure SELECT statement and hard code the variables, it works great, so it's probably my newbie inexperience with passing data.

正如我所提到的，如果我隔离存储过程SELECT语句并对变量进行硬编码，那么它就会工作得很好，因此它可能是我在传递数据方面经验不足的新手。

(@event_type will be used later when I adapt this into a table trigger)

(稍后当我将其调整为表触发器时，将使用@event_type)

Thank you, Kevin

谢谢你,凯文

1 个解决方案

#1

It seems that you have to specify OUTPUT keyword as well, when you call your stored proc:

在调用存储的proc时，似乎还需要指定OUTPUT关键字:

EXECUTE proc_Eligible
    @Control = '3034'
    ,@Checkno = '5011'
    ,@event_type = 'update'
    ,@eligible = @eligible OUTPUT  -- <--- mark parameter as output