Parameterized WHERE clause in SQL Server Stored Procedure

Question

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?

the solution is dynamic sql but your best bet is to narrow down the real required search fields (the answer 'all fields are required' is a lie. period.) and add them as parameters. — Paolo
– Paolo, Commented Apr 27, 2016 at 8:50

Lukasz Szozda · Accepted Answer · 2016-04-25 08:03:08Z

2

Yes, it is possible with Dynamic SQL, but I highly discourage to do that.

SELECT * FROM tbl WHERE @condition:

If you are considering to write the procedure
CREATE PROCEDURE search_sp @condition varchar(8000) AS
   SELECT * FROM tbl WHERE @condition
Just forget it. If you are doing this, you have not completed the transition to use stored procedure and you are still assembling your SQL code in the client.

It will also open your application to SQL Injection attacks.

answered Apr 25, 2016 at 8:03

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Piotr Lasota · Accepted Answer · 2016-04-25 08:06:59Z

1

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

answered Apr 25, 2016 at 8:06

Piotr Lasota

2011 silver badge6 bronze badges

Comments

gbjbaanb · Accepted Answer · 2017-08-05 12:33:51Z

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 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.

Collectives™ on Stack Overflow

Parameterized WHERE clause in SQL Server Stored Procedure

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related