Where clause filter in stored procedure

I’m trying to create a procedure where it needs to filter the records with where condition by multiple scalar parameters. I’m facing the problem with the column I.[status].

In that column the values will be as “pending”, “rejected”, “submitted”. But when the value comes with “All”, I need to select all the status types (pending, rejected, submitted)

CREATE PROCEDURE [dbo].[sp_cc_get_Invoices]   
    (@po_id         INT = NULL,  
     @sortBy        VARCHAR(50) = NULL,  
     @sortDirection VARCHAR(50) = 0,  
     @pageSize      INT = NULL,
     @page          INT = NULL,
     @TotalRows     INT = NULL OUTPUT,  
     @time_zone_offset INT = 0,  
     @vendor_id     VARCHAR(MAX) = NULL,
     @status        VARCHAR(20) = 'All',
     @invoice_id    INT = NULL,
     @invoice_from_dt DATETIME2 = NULL,
     @invoice_to_dt   DATETIME2 = NULL)  
AS  
BEGIN  
    SELECT 
        I.invoice_id, I.invoice_amount, I.[status],
        v.vendor_id, I.po_id, v.vendor_name,    
        I.netsuit_invoice_id, cd.currency_symbol
    FROM    
        invoice_details I   
    LEFT JOIN 
        vendor_details v ON v.vendor_id = I.vendor_id 
    LEFT JOIN 
        currency_details cd ON cd.currency_id = I.currency_id   
    WHERE 
        (@po_id IS NULL OR I.po_id = @po_id) 
        AND (@vendor_id IS NULL OR I.vendor_id = @vendor_id)  
        AND (@invoice_id IS NULL OR @invoice_id = '' OR I.invoice_id = @invoice_id) 
        AND (I.invoice_date BETWEEN @invoice_from_dt AND @invoice_to_dt)  
        AND I.[status] = @status
END

How to filter all the status records?

Answer

You can express this with boolean logic.

Just change:

and I.[status] = @status

To:

and (@status = 'All' or I.[status] = @status)

Advertisement

Answer