SQL – using IF statement after WITH statement

i am making a SQl report, which uses the With statment, and with a lot of inner joins and group etc.

see Working Code below. the code below does work.

the problem is, this table will have output data (as a table) sometime and sometime not based on order intake. when there is not data found, i want to display a specific message of myself, say “dont need to buy pallet”

i want to put a IF statement after the original queue, such as:

IF NewTable is not NULL
  select 
  WorkOrderNumber,
  DispatchDayID,
  sum (NewTable.Qty)as Qty,
  A,
  B,
  PalletDesign
  from NewTable 
  group by WorkOrderNumber,DispatchDayID,A,B,PalletDesign

ELSE 
  select 'dont need to buy pallet'
END

however, if i include the code above, the queue not run, can someone tell me how to put the IF statement after the With as and Select.

(not an SQL expert, sorry if my question not making perfect sense)

————-Working code———–

with NewTable as (

Select 

d.WorkOrderNumber AS WorkOrderNumber,
d.DispatchDayID AS DispatchDayID,
......
.....
....

from Company_OrderDispatchPallet a 

inner join Company_orderDispatchPalletitem b

on a.orderDispatchPalletID = b.orderDispatchPalletID

inner join ......
on ......

inner join ......
on ......

where 
g.PurchasedDate >= dATEADD(hh, +22, cast (CAST(GETDATE()-1 AS DATE) as datetime)) 
and g.PurchasedDate <= dATEADD(hh, +22, cast (CAST(GETDATE()-0 AS DATE) as datetime))  

group by 
a.OrderDispatchid, b.orderItemGuid,c.OrderDispatchID,d.DispatchItemId,
...... )  

— end of NewTable

select 
WorkOrderNumber,
DispatchDayID,
sum (NewTable.Qty)as Qty,
A,
B,
PalletDesign

from NewTable 

group by WorkOrderNumber,DispatchDayID,A,B,PalletDesign

Answer

SQL is not a language that performs logical operations like that, because it is declarative and not procedural.

However, some database systems will offer procedural support, and might have a syntax like:

BEGIN
    DECLARE @check INT;

    SELECT 
        @check = COUNT(1)
    FROM
        NewTable;

    SELECT @check;

    IF @check >= 1
    BEGIN
       select 
       WorkOrderNumber,
       DispatchDayID,
       sum (NewTable.Qty)as Qty,
       A,
       B,
       PalletDesign
       from NewTable 
       group by WorkOrderNumber,DispatchDayID,A,B,PalletDesign
    END
    ELSE
    BEGIN
        PRINT 'dont need to buy pallet';
    END
END