I have a Stored Procedure to get the details of Invoices

Some occasions I get the list of invoices by sending only the InvoiceID 
But in some other occasions I need to get the list of invoices as per the search fields supplied by the user. To do this I send all the fields to the Stored Procedure and use those parameters as below. I included only 2 columns but there are more.

```
SELECT * FROM INVOICES I
    WHERE 
    (@InvoiceNumber is null or  I.InvoiceNumber =  @InvoiceNumber)
    and
    (@PONo is null or I.PONo = @PONo)

```

Is there a way to send the condition for the WHERE clause as one parameter?
['https://Stackoverflow.com/questions/36834939', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/4073943/']
You can use custom type to pass table as parameter <https://msdn.microsoft.com/pl-pl/library/bb510489(v=sql.110).aspx> or you can use default parameters
If you're using SQL Server 2016 or similar (check by calling `select compatibility_level, name from sys.databases` and seeing that your DB is 130 or higher) then you can use the [string\_split](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql) builtin function.

I found it works best like this (spread out for clarity)

```
CREATE PROCEDURE [dbo].[GetInvoices]
  @InvoiceNumber int = NULL
  @PONo nvarchar(1024) = NULL
AS
SELECT * from [Invoices] AS [i] 
WHERE
  i.InvoiceNumber = ISNULL(@InvoiceNunber, i.InvoiceNunber)
  AND CASE 
      WHEN @PONo is null 
      THEN 1 
      ELSE (CASE 
           WHEN i.PONo IN (select value from string_split(@PONo, ',')) 
           THEN 1 
           ELSE 0 
           END) 
      END 
      = 1

```

So if you pass in a null to either parameter it gets translated as `where x = x` which is always true, and if you pass in a CSV value, it selects it from a split table of values that, if present, results in the where clause being `where 1=1`, which is true or `0=1` if the value is not present in the input list.

So here you can pass in an invoice number, or PO number, or both, or neither and it should return what you expect.