Dynamic WHERE statement if stored procedure parameter is null

  • I have the stored procedure with @StartDate and @EndDate parameters

    my WHERE statement is using those parameters to filter out data

    WHERE condition1 and SomeDate >= @StartDate and SomeDate <= @EndDate and condition2

    However when @StartDate or @EndDate is null or empty string then WHERE statement should look like below

    WHERE condition 1 and condition2

    I was trying already to do it with CASE of IF statement but without success so far.

  • Have you tried dynamic SQL?

    Something like this:

    declare @sql nvarchar(max),

    @Startdate nvarchar(10),

    @Enddate nvarchar(10);

    set @sql = 'select * from #table1';

    set @Startdate = '01/01/1900';

    set @Enddate = '01/01/1900';

    if @Startdate is not null and @Startdate <> ''

    and @Enddate is not null and @Enddate <> ''

    set @sql = @sql + ' where Startdate >= ''' + @Startdate + ''' and Enddate <= ''' +@Enddate + ''''

    print @sql

    exec sp_executesql @sql

  • To be honest with you I don't like dynamic sql because of quotation marks '' which are making whole code harder to read 🙂

    So Instead of this I developed something like this:

    where condition1 and (SomeDate >= CASE WHEN @StartDate is null THEN SomeDate ELSE @StartDate END or SomeDate is null)

    and (SomeDate <= CASE WHEN @EndDate is null THEN SomeDate ELSE @EndDate END or SomeDate is null) and condition2

  • Oh be careful here Laurie. You suggested using dynamic sql which is a good choice here. However you committed a cardinal sin. You allowed for the parameters to be executed. This is now a sql injection vulnerability. You should instead use parameters to your dynamic sql.

    Here is a full working example.

    create table #table1

    (

    StartDate datetime,

    EndDate datetime

    )

    insert #table1

    select '1/15/1900', '2/1/1900'

    declare @sql nvarchar(max),

    @Startdate nvarchar(10),

    @Enddate nvarchar(10);

    set @sql = 'select * from #table1';

    set @Startdate = '01/01/1900';

    set @Enddate = '01/01/1901';

    if @Startdate > '' AND @Enddate > ''

    set @sql = @sql + ' where Startdate >= @Startdate and Enddate <= @Enddate'

    print @sql

    exec sp_executesql @sql, N'@Startdate datetime, @EndDate datetime', @Startdate = @Startdate, @Enddate = @Enddate

    drop table #table1

    _______________________________________________________________

    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/

  • phoenix_ (7/10/2013)


    To be honest with you I don't like dynamic sql because of quotation marks '' which are making whole code harder to read 🙂

    So Instead of this I developed something like this:

    where condition1 and (SomeDate >= CASE WHEN @StartDate is null THEN SomeDate ELSE @StartDate END or SomeDate is null)

    and (SomeDate <= CASE WHEN @EndDate is null THEN SomeDate ELSE @EndDate END or SomeDate is null) and condition2

    This will work to a point. You will end up with some serious performance issues with this type of query though. Check out this article from Gail on the topic.

    http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]

    _______________________________________________________________

    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/

  • I use this syntax in my where clauses rather than case statements:

    WHERE

    ((@StartDate IS NOT NULL AND StartDate >= @StartDate)

    OR

    (@StartDate IS NULL))

    AND ((@EndDate IS NOT NULL AND EndDate <= @EndDate)

    OR

    (@EndDate IS NULL))

  • Steven Willis (7/10/2013)


    I use this syntax in my where clauses rather than case statements:

    WHERE

    ((@StartDate IS NOT NULL AND StartDate >= @StartDate)

    OR

    (@StartDate IS NULL))

    AND ((@EndDate IS NOT NULL AND EndDate <= @EndDate)

    OR

    (@EndDate IS NULL))

    You too might want to read that article from Gail. This type of approach can lead to really awful performance.

    _______________________________________________________________

    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/

  • Sean Lange (7/10/2013)


    phoenix_ (7/10/2013)


    To be honest with you I don't like dynamic sql because of quotation marks '' which are making whole code harder to read 🙂

    So Instead of this I developed something like this:

    where condition1 and (SomeDate >= CASE WHEN @StartDate is null THEN SomeDate ELSE @StartDate END or SomeDate is null)

    and (SomeDate <= CASE WHEN @EndDate is null THEN SomeDate ELSE @EndDate END or SomeDate is null) and condition2

    This will work to a point. You will end up with some serious performance issues with this type of query though. Check out this article from Gail on the topic.

    http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]

    +1 to you Sean for the link to the bible on this topic.

    I saw the subject of this thread and visited to make sure someone had already provided it.


    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

  • Thanks Sean for that link of Gail 🙂

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • If we have multiple parameters in one stored procedure and if any of those parameters has value empty, we should not us skip using a condition related to that parameter in where clause. How to achieve that? Below is the query:

    If any parameter value is passed as empty, that parameter related condition should not be used in the below where clause.

    CREATE PROCEDURE [dbo].[MC1_MSS_GET_FD_IQ_SEARCHORDERS]

    @clientcode CHAR(3),

    @ordernumber MSS_MC1_LookUpIds READONLY,

    @fromdate DATETIME,

    @todate DATETIME,

    @orderstatus_code VARCHAR(256),

    @permmasterlistid MSS_MC1_LookUpIds READONLY,

    @batchnumber VARCHAR(128),

    @externalordernumber MSS_MC1_LookUpIds READONLY,

    @firstname VARCHAR(256),

    @lastname VARCHAR(256),

    @plannumber VARCHAR(128),

    @halfilename MSS_MC1_LookUpIds READONLY,

    @PageNum INT,

    @PageSize INT,

    @sortQuery VARCHAR(150),

    @return_code CHAR(03) OUTPUT,

    @return_msg VARCHAR(255) OUTPUT

    AS

    select POH.PermMasterListId,

    BH.BatchNumber,

    POH.OP_OrderNumber,

    POH.C_FirstName,

    POH.C_LastName,

    POH.ExternalOrderNumber,

    CAST(POH.CreateDate AS DATE) AS OrderDate,

    OT.OrderPersonalizationCode,

    POH.ShipMethod,

    null as element_data,

    PS.StatusName,

    (select count(por.PermmasterListId) from tblIQPermorderRecipient por

    where por.PermMasterListId=POH.PermmasterListId GROUP By por.PermmasterListId) AS RecipientCount

    FROM tblIQPermOrderHeader POH LEFT JOIN tblIQBatchHeader BH WITH (NOLOCK) ON POH.BatchID = BH.BatchID

    inner join tblIQPermOrderLine POL WITH (NOLOCK) ON POH.PermMasterListId = POL.PermMasterListId

    LEFT JOIN tblIQProcessStatus PS WITH (NOLOCK) ON POH.statuscode= PS.statuscode

    LEFT JOIN tblIqOrderType OT WITH (NOLOCK) ON POH.OrderTypeID= OT.OrderTypeID

    LEFT JOIN tbliqclienttypes ict WITH (NOLOCK) ON POH.clientTypeid = ict.ClientTypeID

    LEFT JOIN tbliqclient ic WITH (NOLOCK) ON ict.clientid = ic.clientid

    LEFT JOIN tblIQItems it WITH (NOLOCK) ON it.ItemCode = pol.ItemCode and it.ClientId = ic.ClientID

    LEFT JOIN tblIQItemAttributes iqitemattr WITH (NOLOCK) ON (iqitemattr.ItemId = it.ItemId and iqitemattr.AttributeId = @plannumberAttributeid)

    WHERE

    ic.ClientCode=@clientcode AND

    ((@orderCount = 0) OR POH.OP_OrderNumber in (SELECT lookUpIds FROM @ordernumber)) AND

    ((ISNULL(@fromdate, '') = '') OR CAST(POH.CreateDate AS DATE) >=@fromdate) AND

    ((ISNULL(@todate, '') = '') OR CAST(POH.CreateDate AS DATE) <=@todate) AND

    ((ISNULL(@plannumber, '') = '') OR (iqitemattr.AttributeValue = @plannumber)) AND

    ((@permmasterlistidCount = 0) OR POH.PermMasterListId in (SELECT lookUpIds FROM @permmasterlistid))AND

    ((ISNULL(@batchnumber, '') = '') OR BH.BatchNumber=@batchnumber) AND

    ((@extordernumCount = 0) OR POH.ExternalOrderNumber in (SELECT lookUpIds FROM @externalordernumber))AND

    ((ISNULL(@firstname, '') = '') OR POH.C_FirstName=@firstname) AND

    ((ISNULL(@lastname, '') = '') OR POH.C_LastName=@lastname) AND

    ((ISNULL(@orderstatus_code, '') = '') OR POL.statuscode=@orderstatus_code)

    GROUP BY

    POH.PermMasterListId,

    BH.BatchNumber,

    POH.OP_OrderNumber,

    POH.C_FirstName,

    POH.C_LastName,

    POH.ExternalOrderNumber,

    POH.CreateDate,

    POH.ShipMethod,

    OT.OrderPersonalizationCode,

    PS.StatusName

  • GeethaReddy wrote:

    If we have multiple parameters in one stored procedure and if any of those parameters has value empty, we should not us skip using a condition related to that parameter in where clause. How to achieve that?

    For starters you should start your own thread instead of hijacking one from 8 years ago. I would also suggest you not use the NOLOCK hint unless you are ok with sometimes returning missing and/or duplicate rows.

    For the question at hand you need to read these two articles which explains how to tackle these kinds of queries.

    https://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

    https://sqlinthewild.co.za/index.php/2018/03/13/revisiting-catch-all-queries/

    _______________________________________________________________

    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/

  • Thank you very much for the response and the links. They really helped me but I have one more question now.

    I am using multiple TVPs in my stored procedure and used those TVPs in dynamic sql. The execution is throwing an error: 'Procedure or function has too many arguments specified.'

    I declared the TVPs as @ordernumber MSS_MC1_LookUpIds READONLY, @permmasterlistid MSS_MC1_LookUpIds READONLY, @externalordernumber MSS_MC1_LookUpIds READONLY

    Used these TVPs in dynamic sql. And to execute, I am using

    EXEC sp_executesql @sqlToExec, N'@ordernumber MSS_MC1_LookUpIds READONLY', @ordernumber,

    N'@permmasterlistid MSS_MC1_LookUpIds READONLY', @permmasterlistid,

    N'@externalordernumber MSS_MC1_LookUpIds READONLY', @externalordernumber

     

    Which is throwing an error. When I give just one TVP to execute, it works fine - EXEC sp_executesql @sqlToExec, N'@ordernumber MSS_MC1_LookUpIds READONLY', @ordernumber

    How to use these three TVPs in sp_executesql?

  • The answer for this is:

    EXEC sp_executesql @sqlToExec, N'@ordernumber MSS_MC1_LookUpIds READONLY, @permmasterlistid MSS_MC1_LookUpIds READONLY, @externalordernumber MSS_MC1_LookUpIds READONLY',

    @ordernumber, @permmasterlistid, @externalordernumber

Viewing 13 posts - 1 through 12 (of 12 total)

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