Dynamic Where Clause for Multiple paramaters with AND Operator

  • Hi I am Trying to Build Dynamic Where Clause for Multiple Parameters and they could be Null Also .Please find my

    Query as Below and I need to make AND Operator to be dynamic instead of hard coding it as i was doing it as below

    so the query will look as select * from xyx where cu.unitNumber like '%@SearchValue%' AND cd.CaseDispoID=5 and StartDate >= '12/01/2012' and EndDate<=GetDate()

    Thanks In Advance...

    SELECT @Where =CASE @SearchBy

    WHEN 1 THEN 'WHERE CAST(Ce.CustomerEventID AS VARCHAR)'

    WHEN 2 THEN 'WHERE u.UnitNumber'

    WHEN 3 THEN 'WHERE l.LocationNumber'

    WHEN 4 THEN 'WHERE q.SPName'

    WHEN 5 THEN 'WHERE ce.UserName'

    WHEN 6 THEN 'WHERE c.City + c.StateName'

    WHEN 7 THEN 'WHERE ce.UserName'

    WHEN 8 THEN 'WHERE cu.Name'

    WHEN 9 THEN 'WHERE cu.CustomerNumber'

    ELSE

    'WHERE'

    END

    IF(@SearchValue IS NOT NULL)

    BEGIN

    SET @WhSearchValue ='LIKE ''%'+@SearchValue+'%'' '

    END

    ELSE

    BEGIN

    SET @WhSearchValue =' LIKE''%'++'%'' '

    END

    IF(@CaseDispoID IS NOT NULL)

    BEGIN

    SET @WhCaseDispoID =' AND cd.CaseDispoID='+CAST(@CaseDispoID AS VARCHAR)+''

    END

    ELSE

    BEGIN

    SET @WhCaseDispoID=''

    END

    IF(@StartDate IS NOT NULL)

    BEGIN

    SET @WhStartDate ='AND ce.CreationTime>='''+CAST(@StartDate AS VARCHAR)+''''

    END

    ELSE

    BEGIN

    SET @WhStartDate=''

    END

    IF(@EndDate IS NOT NULL)

    BEGIN

    SET @WhEndDate ='AND ce.CreationTime<='''+CAST(@EndDate AS VARCHAR)+''''

    END

    ELSE

    BEGIN

    SET @WhEndDate ='AND ce.CreationTime <= GetDate()'

    END

    SELECT @SQL= '

    '+@Select+'

    '+@From+'

    '+@Where+'

    '+@WhSearchValue+'

    '+@WhCaseDispoID+'

    '+@WhStartDate+'

    '+@WhEndDate+'

    '

    EXEC (@SQL);

  • Other than Case 6, do you expect to have the ability to have multiple conditions in the where clause?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • I will not be expecting any other parameters ..Only i do have only 6 parameters in where Clause

  • RamSteve (1/2/2013)


    I will not be expecting any other parameters ..Only i do have only 6 parameters in where Clause

    So of the 9 parameters you listed, which 6 are possible? Or are you saying that you can have up to 6 parameters at once?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • HI in the above 9 parameters it can have only one based on case statement or it could be NULL for example

    In @Where parameter i was giving cu.customerNumber Like '%1234%' or ce.CustomerEventID Like '%12334%' ..etc

    So @Where parameter and @SerchValue are related and Rest of the parameters (@CaseDispoID ,StartDate ,EndDate) can have one value or could be NULL so the final query will be like below if you have all the parameters

    select * from abc Where ce.CustomerEventID Like '%12345%' and cd.CaseDispoID=5 AND ce.StartDate >='12/01/2012' and ce.EndDate<= GETDATE

    So for example in the above Query IF the Parameters @Where and @SearchValue is NULL then the query looks like below

    select * from abc where cd.CaseDispoID=5 AND ce.StartDate >='12/01/2012' and ce.EndDate<= GETDATE

    So my whole point is to build Dynamic Where Clause Query using AND Operator based on parameters i get in case statement for each parameter(@Where,@WhSearchValue,@WhCaseDispoID,@WhStartDate,@WhEndDate)

  • RamSteve (1/2/2013)


    Hi I am Trying to Build Dynamic Where Clause for Multiple Parameters and they could be Null Also .Please find my

    Query as Below and I need to make AND Operator to be dynamic instead of hard coding it as i was doing it as below

    so the query will look as select * from xyx where cu.unitNumber like '%@SearchValue%' AND cd.CaseDispoID=5 and StartDate >= '12/01/2012' and EndDate<=GetDate()

    Thanks In Advance...

    SELECT @Where =CASE @SearchBy

    WHEN 1 THEN 'WHERE CAST(Ce.CustomerEventID AS VARCHAR)'

    WHEN 2 THEN 'WHERE u.UnitNumber'

    WHEN 3 THEN 'WHERE l.LocationNumber'

    WHEN 4 THEN 'WHERE q.SPName'

    WHEN 5 THEN 'WHERE ce.UserName'

    WHEN 6 THEN 'WHERE c.City + c.StateName'

    WHEN 7 THEN 'WHERE ce.UserName'

    WHEN 8 THEN 'WHERE cu.Name'

    WHEN 9 THEN 'WHERE cu.CustomerNumber'

    ELSE

    'WHERE'

    END

    IF(@SearchValue IS NOT NULL)

    BEGIN

    SET @WhSearchValue ='LIKE ''%'+@SearchValue+'%'' '

    END

    ELSE

    BEGIN

    SET @WhSearchValue =' LIKE''%'++'%'' '

    END

    IF(@CaseDispoID IS NOT NULL)

    BEGIN

    SET @WhCaseDispoID =' AND cd.CaseDispoID='+CAST(@CaseDispoID AS VARCHAR)+''

    END

    ELSE

    BEGIN

    SET @WhCaseDispoID=''

    END

    IF(@StartDate IS NOT NULL)

    BEGIN

    SET @WhStartDate ='AND ce.CreationTime>='''+CAST(@StartDate AS VARCHAR)+''''

    END

    ELSE

    BEGIN

    SET @WhStartDate=''

    END

    IF(@EndDate IS NOT NULL)

    BEGIN

    SET @WhEndDate ='AND ce.CreationTime<='''+CAST(@EndDate AS VARCHAR)+''''

    END

    ELSE

    BEGIN

    SET @WhEndDate ='AND ce.CreationTime <= GetDate()'

    END

    SELECT @SQL= '

    '+@Select+'

    '+@From+'

    '+@Where+'

    '+@WhSearchValue+'

    '+@WhCaseDispoID+'

    '+@WhStartDate+'

    '+@WhEndDate+'

    '

    EXEC (@SQL);

    I see a couple of issues here:

    1. Note where I have made some of your script bold. In those cases, you are missing a blank before either the AND or the LIKE.

    2. This CAST of the DATETIME variable may not give you a format the SQL can easily compare (and it is likely to be truncated):

    CAST(@EndDate AS VARCHAR)

    You should use this instead:

    CAST(VARCHAR(19), @EndDate,120)

    Or if additional precision is required (milliseconds) use:

    CAST(VARCHAR(23), @EndDate,121)

    You can also significantly condense all the statements after the assignment to @WhSearchValue, something like this:

    SELECT @WhCaseDispoID=''

    ,@WhStartDate=''

    ,@WhEndDate=''

    IF @CaseDispoID IS NOT NULL

    SET @WhCaseDispoID =' AND cd.CaseDispoID='+CAST(@CaseDispoID AS VARCHAR)+''

    ELSE IF @StartDate IS NOT NULL

    SET @WhStartDate =' AND ce.CreationTime>='''+CAST(VARCHAR(19), @StartDate,120)+''''

    ELSE IF @EndDate IS NOT NULL

    SET @WhEndDate =' AND ce.CreationTime<='''+CAST(VARCHAR(19), @EndDate,120)+''''

    ELSE SET @WhEndDate =' AND ce.CreationTime <GetDate()'

    That last assuming of course that only one of the 3 input parameters is NOT NULL.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • So any chance of building the Dynamic Where Clause Query for the above statements assuming that some parameters could be NULL ..Thanks In Advance

  • Take a look at Gail's post. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]

    She explains how to deal with this type of thing very cleanly.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • dwain.c (1/2/2013)

    You should use this instead:

    CAST(VARCHAR(19), @EndDate,120)

    Or if additional precision is required (milliseconds) use:

    CAST(VARCHAR(23), @EndDate,121)

    I think that should have been convert, not cast. I'm sure just a typo. 120 also assumes you are using MDY ordering.

    I am wondering about this part in the original query (and similar parts):

    'AND ce.CreationTime>='''+CAST(@StartDate AS VARCHAR)+''''

    If the CreationTime field is a datetime, then you should compare it to @StartDate parameter as a datetime. Apart from NULLs it should then behave as intended.

    If the CreationTime field is a varchar, you might be safer to cast CreationTime as a datetime before comparing it to @StartDate, rather than casting @StartDate as a varchar.

    Comparing them both as varchars may well implicitly convert both to datetime values prior to comparison but you can't guarantee that and it might give unexpected results, particularly if this is a manually entered field in the front end application which could have dodgy values entered.

  • davoscollective (1/6/2013)


    dwain.c (1/2/2013)

    You should use this instead:

    CAST(VARCHAR(19), @EndDate,120)

    Or if additional precision is required (milliseconds) use:

    CAST(VARCHAR(23), @EndDate,121)

    I think that should have been convert, not cast. I'm sure just a typo.

    Yup. You're correct.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply