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.

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