Splitting a sql stament based on where clause

• sql_novice_2007

  Hall of Fame

  Points: 3307

  More actions

  July 18, 2012 at 1:20 pm

  #259581

  In a stored procedure I have a dynamic sql statement Which will be like the following.

  SELECT * FROM table1 WHERE property1 =’test’ AND property2=’real’

  AND property3=’test’ AND property4=’fake’ AND property5=’test’ AND property6=’test’

  If the property value=’test’ I need to split the above sql query into two queries as follow. In this case the above sql statement should be divided into two queries like the following.

  SELECT * FROM table1 WHERE property1 =’test’

  AND property3=’test’ AND property5=’test’ AND property6=’test’

  SELECT * FROM table1 WHERE property2=’real’

  AND property4=’fake’

  All the conditions with property value test should be in one query and all the other property values should be in another query.

  There are 22 properties in the table and all the 22 properties can be used in the where clause.

  Any help would be appreciated.

  Thanks.

• Jared

  SSC Guru

  Points: 61793

  More actions

  July 18, 2012 at 1:25 pm

  #1514503

  Take a look at this blog post. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

  Jared
  CE - Microsoft