CODEWITHSUNDEEP
sql-serverstored-procedures
GettingStrongerEveryDay
GettingStrongerEveryDay

Reputation: 31

T-SQL Stored Procedure passing input, using output

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

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:

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).

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.

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

Thank you, Kevin

Upvotes: 2

Views: 2436

Answers (1)

i-one
i-one

Reputation: 5120

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

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

Upvotes: 5

Related Questions