T-SQL Stored Procedure passing input, using output

Question

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

Do you mean that if you run the select on its own with hardcoded values instead of variables it returns at least one row but if you call the proc passing in the same values as parameters, the resultset is empty? — acfrancis
– acfrancis, Commented Oct 22, 2013 at 21:34

i-one · Accepted Answer · 2013-10-22 21:35:14Z

5

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

answered Oct 22, 2013 at 21:35

i-one

5,1201 gold badge33 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

GettingStrongerEveryDay Over a year ago

Thank you! I swear I tried that, but as with a lot of programming goes (which I've been doing for years, just not in this language), it tends to be a combination of things. <d'oh!>

Collectives™ on Stack Overflow

T-SQL Stored Procedure passing input, using output

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related