Return new query if rowcount = 0

Question

How can I return a new query if my first query didn't return anything? I'm using ssis to execute a stored procedure, if the stored procedure didn't return anything it should pass a new query that will be then saved to a new ole db destination.

Sample Query:

Declare @DepartureDate DATETIME = '4/16/2013',

begin 

select PassengerNumber,FromTime,ToTime,Remarks from table a where DepartureDate = @DepartureDate



if (@@ROWCOUNT = 0)
    begin


      Select
        '-' [PassengerNumber],
        '00:00' [FromTime],
        '00:00' [ToTime],
        'No Mismatch' [Remarks]
    end
End

My problem is that my ole db source returns the blank query instead of the new query provided by the if(@@rowcount = 0). So no data is transferred to the ole db source.

John Woo · Accepted Answer · 2013-04-30T03:00:52.703

1

how about using UNION?

SELECT  PassengerNumber, FromTime, ToTime, Remarks 
FROM    tableA 
WHERE   DepartureDate = @DepartureDate
UNION   ALL
SELECT  '-' [PassengerNumber],
        '00:00' [FromTime],
        '00:00' [ToTime],
        'No Mismatch' [Remarks]
WHERE   0 = (SELECT COUNT(*) FROM tableA WHERE DepartureDate = @DepartureDate)

edited Apr 30 '13 at 03:00

answered Apr 30 '13 at 02:55

John Woo

258,903
69
498
492

NOT EXISTS might be a little quicker: `WHERE NOT EXISTS (SELECT 1 FROM tableA WHERE DepartureDate = @DepartureDate)` – Nick.Mc Apr 30 '13 at 02:58
and `UNION ALL` is even more likely to be quicker. Never use `UNION` when you can use `UNION ALL` – Nick.Mc Apr 30 '13 at 03:00
@ElectricLlama right, there is no sense of using `UNION` here. – John Woo Apr 30 '13 at 03:01

score 1 · Answer 2 · answered Apr 30 '13 at 03:07

DECLARE @count INT = -1;
SELECT @count = COUNT(*)FROM TABLE a;

IF (@count > 0)
BEGIN
    SELECT PassengerNumber,FromTime,ToTime,Remarks FROM TABLE a;
END
ELSE
    SELECT
        '-' [PassengerNumber],
        '00:00' [FromTime],
        '00:00' [ToTime],
        'No Mismatch' [Remarks];
    END
END

Return new query if rowcount = 0

2 Answers2