none
Dynamic where condition

    Question

  • My query will be something like,

    select accountid,accountdesc where remark like  '%'  

    sselect accountid,accountdesc where remark like  'MRE%' 

    select accountid,accountdesc where remark like  'AHE%' 

    select accountid,accountdesc where remark not like  'MRE%'  and remark not like 'AHE%'

    where 'where remark' depends on what user pass value in other parameter say flag,

    i want to combine all this into one. So i want something like,

     select accountid,accountdesc where @cond

    if flag='M' 

      @cond = remark like  'MRE%' 

    if flag='All'

      @cond = remark like  '%'

    if flag = 'Manual'

      @cond = remark not like  'MRE%' and remark not like 'AHE%'

    etc......


    h2007

    Friday, November 15, 2013 4:00 AM

Answers

  • SELECT  accountid ,
            accountdesc
    WHERE   
    ( flag='M' AND remark like 'MRE%' )
    OR
    ( flag='All' AND remark like  '%')


    sqldevelop.wordpress.com

    • Marked as answer by h2007 Monday, November 18, 2013 3:38 AM
    Friday, November 15, 2013 7:21 AM

All replies

  • Try dynamic sql. 

    Use build the whole sql dynamically and pass to EXECUTE OR SP_EXECUTESQL

    http://www.techrepublic.com/blog/the-enterprise-cloud/generate-dynamic-sql-statements-in-sql-server/

    http://technet.microsoft.com/en-us/library/ms188001.aspx


    Regards Harsh

    Friday, November 15, 2013 4:40 AM
  • SELECT  accountid ,
            accountdesc
    WHERE   
    ( flag='M' AND remark like 'MRE%' )
    OR
    ( flag='All' AND remark like  '%')


    sqldevelop.wordpress.com

    • Marked as answer by h2007 Monday, November 18, 2013 3:38 AM
    Friday, November 15, 2013 7:21 AM
  • h2007,

    Since you have not posted the DDL so I can not confirm that the following approach will work or not. But still you can test it. Check if this works.

    SELECT *
    FROM Table
    WHERE CASE WHEN Flag='M' THEN Remark END LIKE 'MRE%'
    OR CASE WHEN Flag='All' THEN Remark END LIKE '%'
    OR CASE WHEN Flag='Manual' THEN Remark END LIKE 'AHE%'


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, November 15, 2013 7:22 AM
  • I would have done it like this without using dynamic sql as your search column is the same

    select accountid,accountdesc
    from table
    where (remark like 'MRE%' OR @Flag IN ('M','All'))
    AND (remark like 'AHE%' OR @Flag = 'All')

    Friday, November 15, 2013 8:02 AM
  • h2007,

    Since you have not posted the DDL so I can not confirm that the following approach will work or not. But still you can test it. Check if this works.

    SELECT *
    FROM Table
    WHERE CASE WHEN Flag='M' THEN Remark END LIKE 'MRE%'
    OR CASE WHEN Flag='All' THEN Remark END LIKE '%'
    OR CASE WHEN Flag='Manual' THEN Remark END LIKE 'AHE%'


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    I want to write

    case when flag = 'manual' then remark end  "not like 'MRE%' and not like 'AHE%'  "  (how to do this?)


    h2007

    Monday, November 18, 2013 5:48 AM
  • I would have done it like this without using dynamic sql as your search column is the same

    select accountid,accountdesc
    from table
    where (remark like 'MRE%' OR @Flag IN ('M','All'))
    AND (remark like 'AHE%' OR @Flag = 'All')

    sorry i couldn't understand your code.


    h2007

    Monday, November 18, 2013 5:53 AM