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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mish
  • 35
  • 6
  • Google `SQL Server catch all queries`. – Zohar Peled May 10 '18 at 07:31
  • How [A, B, C, D] parameters are related to the table if there are columns [Col1, Col2, Col3 and Col4]? – Maciej Los May 10 '18 at 07: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. – Jodrell May 10 '18 at 07:34
  • @Jodrell I would say that it's *one* point, not *the* point. There are other benefits as well. – Zohar Peled May 10 '18 at 07: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. – Jodrell May 10 '18 at 07:54

0 Answers0