0

I need a stored procedure which can filter data based on multiple parameters. Say there are 4 parameters(A,B,C & D). The user can select any number of them. Like they can select only A or A & C or all 4.

For example:

  1. If the user selects only A, then it should be like:

    select Col1, Col2, Col3, Col4, Col5 
    from tbl1 
    where A = 123
    
  2. If the user selects parameters B & D, then it should be like:

    select col1, col2, col3, col4, col5 
    from tbl1 
    where B = 'final' and D = '1' 
    

How to write such a dynamic query with a where clause where the conditions change as per the selection?

5
  • Google SQL Server catch all queries. Commented May 10, 2018 at 7:31
  • How [A, B, C, D] parameters are related to the table if there are columns [Col1, Col2, Col3 and Col4]? Commented May 10, 2018 at 7:33
  • My advice would be, don't write a stored procedure that does significantly different queries based on parameters. The point of using a SP is to get the benefit of a cached query plan, but that will likely be bad in your scenario. Why not build your query in the calling code before sending the query to the server. Commented May 10, 2018 at 7:34
  • @Jodrell I would say that it's one point, not the point. There are other benefits as well. Commented May 10, 2018 at 7:36
  • @ZoharPeled, I concede there are other benefits but, the idea that a Stored Procedure is a good place to dynamically generate a query is wrong. String building and manipulation are not a strength of TSQL. Commented May 10, 2018 at 7:54

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.