How to not repeat yourself with SQL where clause

up vote
2
down vote

favorite

I have a stored procedure which includes this:

declare @dueTypeCode nvarchar(3) = 'ALL' --Is actually a parameter that is passed into the stored procedure

declare @today DateTime = GetUtcDate();

declare @tomorrow DateTime = dateadd(day,datediff(day,-1,GETUTCDATE()),0);

declare @fiveDaysFromNow DateTime = dateadd(day,datediff(day,-5,GETUTCDATE()),0);



if(@dueTypeCode = 'ALL')



    Select * from Items



ELSE IF (@dueTypeCode = 'TODAY')



    Select * from Items where DueDate =  @today



ELSE IF (@dueTypeCode = 'NEXT5DAYS')    



    Select *  from Items where  DueDate  >= @today and DueDate <= @fiveDaysFromNow 

END

Is there any way to wrap this into a single SQL statement to avoid repetition of the above code?

The actual select statement is much more complicated, and repeating it 3 times just to vary the where clause seems a violation of the DRY principle.

sql sql-server tsql datetime sql-server-2016

share|improve this question

edited 2 days ago

Salman A

171k65328414

asked Nov 19 at 12:12

Greg Gum

9,8431573124

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Have you considered using Dynamic SQL or putting all the logic in a single WHERE and include the RECOMPILE option?
  – Larnu
  Nov 19 at 12:14
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  yes and no. You can use boolean logic to control which part of the logic wins according to what @dueTypeCode is set to, but then you are taking the risk of creating an inefficient query. The greater complexity of the logic may confound the query optimiser. So watch out for 'catch all' queries becoming inefficient.
  – Cato
  Nov 19 at 12:16
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  @Larnu, No, I had not considered Dynamic Sql. Yes, I have considered the single where clause, but not sure how to do that.
  – Greg Gum
  Nov 19 at 12:16
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  Peruse this dynamic search conditions article.
  – Dan Guzman
  Nov 19 at 12:21
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  @DanGuzman, that is exactly what I was looking for, thank you.
  – Greg Gum
  Nov 19 at 12:31
  

  
  
  
  

add a comment | 

up vote
2
down vote

favorite

I have a stored procedure which includes this:

declare @dueTypeCode nvarchar(3) = 'ALL' --Is actually a parameter that is passed into the stored procedure

declare @today DateTime = GetUtcDate();

declare @tomorrow DateTime = dateadd(day,datediff(day,-1,GETUTCDATE()),0);

declare @fiveDaysFromNow DateTime = dateadd(day,datediff(day,-5,GETUTCDATE()),0);



if(@dueTypeCode = 'ALL')



    Select * from Items



ELSE IF (@dueTypeCode = 'TODAY')



    Select * from Items where DueDate =  @today



ELSE IF (@dueTypeCode = 'NEXT5DAYS')    



    Select *  from Items where  DueDate  >= @today and DueDate <= @fiveDaysFromNow 

END

Is there any way to wrap this into a single SQL statement to avoid repetition of the above code?

The actual select statement is much more complicated, and repeating it 3 times just to vary the where clause seems a violation of the DRY principle.

sql sql-server tsql datetime sql-server-2016

share|improve this question

edited 2 days ago

Salman A

171k65328414

asked Nov 19 at 12:12

Greg Gum

9,8431573124

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Have you considered using Dynamic SQL or putting all the logic in a single WHERE and include the RECOMPILE option?
  – Larnu
  Nov 19 at 12:14
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  yes and no. You can use boolean logic to control which part of the logic wins according to what @dueTypeCode is set to, but then you are taking the risk of creating an inefficient query. The greater complexity of the logic may confound the query optimiser. So watch out for 'catch all' queries becoming inefficient.
  – Cato
  Nov 19 at 12:16
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  @Larnu, No, I had not considered Dynamic Sql. Yes, I have considered the single where clause, but not sure how to do that.
  – Greg Gum
  Nov 19 at 12:16
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  Peruse this dynamic search conditions article.
  – Dan Guzman
  Nov 19 at 12:21
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  @DanGuzman, that is exactly what I was looking for, thank you.
  – Greg Gum
  Nov 19 at 12:31
  

  
  
  
  

add a comment | 

up vote
2
down vote

favorite

up vote
2
down vote

favorite

I have a stored procedure which includes this:

declare @dueTypeCode nvarchar(3) = 'ALL' --Is actually a parameter that is passed into the stored procedure

declare @today DateTime = GetUtcDate();

declare @tomorrow DateTime = dateadd(day,datediff(day,-1,GETUTCDATE()),0);

declare @fiveDaysFromNow DateTime = dateadd(day,datediff(day,-5,GETUTCDATE()),0);



if(@dueTypeCode = 'ALL')



    Select * from Items



ELSE IF (@dueTypeCode = 'TODAY')



    Select * from Items where DueDate =  @today



ELSE IF (@dueTypeCode = 'NEXT5DAYS')    



    Select *  from Items where  DueDate  >= @today and DueDate <= @fiveDaysFromNow 

END

Is there any way to wrap this into a single SQL statement to avoid repetition of the above code?

The actual select statement is much more complicated, and repeating it 3 times just to vary the where clause seems a violation of the DRY principle.

sql sql-server tsql datetime sql-server-2016

share|improve this question

edited 2 days ago

Salman A

171k65328414

asked Nov 19 at 12:12

Greg Gum

9,8431573124

I have a stored procedure which includes this:

declare @dueTypeCode nvarchar(3) = 'ALL' --Is actually a parameter that is passed into the stored procedure

declare @today DateTime = GetUtcDate();

declare @tomorrow DateTime = dateadd(day,datediff(day,-1,GETUTCDATE()),0);

declare @fiveDaysFromNow DateTime = dateadd(day,datediff(day,-5,GETUTCDATE()),0);



if(@dueTypeCode = 'ALL')



    Select * from Items



ELSE IF (@dueTypeCode = 'TODAY')



    Select * from Items where DueDate =  @today



ELSE IF (@dueTypeCode = 'NEXT5DAYS')    



    Select *  from Items where  DueDate  >= @today and DueDate <= @fiveDaysFromNow 

END

Is there any way to wrap this into a single SQL statement to avoid repetition of the above code?

The actual select statement is much more complicated, and repeating it 3 times just to vary the where clause seems a violation of the DRY principle.

sql sql-server tsql datetime sql-server-2016

sql sql-server tsql datetime sql-server-2016

share|improve this question

edited 2 days ago

Salman A

171k65328414

asked Nov 19 at 12:12

Greg Gum

9,8431573124

share|improve this question

edited 2 days ago

Salman A

171k65328414

asked Nov 19 at 12:12

Greg Gum

9,8431573124

share|improve this question

share|improve this question

edited 2 days ago

Salman A

171k65328414

edited 2 days ago

Salman A

171k65328414

edited 2 days ago

Salman A

171k65328414

171k65328414

asked Nov 19 at 12:12

Greg Gum

9,8431573124

asked Nov 19 at 12:12

Greg Gum

9,8431573124

asked Nov 19 at 12:12

Greg Gum

9,8431573124

9,8431573124

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Have you considered using Dynamic SQL or putting all the logic in a single WHERE and include the RECOMPILE option?
  – Larnu
  Nov 19 at 12:14
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  yes and no. You can use boolean logic to control which part of the logic wins according to what @dueTypeCode is set to, but then you are taking the risk of creating an inefficient query. The greater complexity of the logic may confound the query optimiser. So watch out for 'catch all' queries becoming inefficient.
  – Cato
  Nov 19 at 12:16
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  @Larnu, No, I had not considered Dynamic Sql. Yes, I have considered the single where clause, but not sure how to do that.
  – Greg Gum
  Nov 19 at 12:16
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  Peruse this dynamic search conditions article.
  – Dan Guzman
  Nov 19 at 12:21
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  @DanGuzman, that is exactly what I was looking for, thank you.
  – Greg Gum
  Nov 19 at 12:31
  

  
  
  
  

add a comment | 

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Have you considered using Dynamic SQL or putting all the logic in a single WHERE and include the RECOMPILE option?
  – Larnu
  Nov 19 at 12:14
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  yes and no. You can use boolean logic to control which part of the logic wins according to what @dueTypeCode is set to, but then you are taking the risk of creating an inefficient query. The greater complexity of the logic may confound the query optimiser. So watch out for 'catch all' queries becoming inefficient.
  – Cato
  Nov 19 at 12:16
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  @Larnu, No, I had not considered Dynamic Sql. Yes, I have considered the single where clause, but not sure how to do that.
  – Greg Gum
  Nov 19 at 12:16
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  Peruse this dynamic search conditions article.
  – Dan Guzman
  Nov 19 at 12:21
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  @DanGuzman, that is exactly what I was looking for, thank you.
  – Greg Gum
  Nov 19 at 12:31
  

  
  
  
  

Have you considered using Dynamic SQL or putting all the logic in a single WHERE and include the RECOMPILE option?
– Larnu
Nov 19 at 12:14

Have you considered using Dynamic SQL or putting all the logic in a single WHERE and include the RECOMPILE option?
– Larnu
Nov 19 at 12:14

yes and no. You can use boolean logic to control which part of the logic wins according to what @dueTypeCode is set to, but then you are taking the risk of creating an inefficient query. The greater complexity of the logic may confound the query optimiser. So watch out for 'catch all' queries becoming inefficient.
– Cato
Nov 19 at 12:16

yes and no. You can use boolean logic to control which part of the logic wins according to what @dueTypeCode is set to, but then you are taking the risk of creating an inefficient query. The greater complexity of the logic may confound the query optimiser. So watch out for 'catch all' queries becoming inefficient.
– Cato
Nov 19 at 12:16

@Larnu, No, I had not considered Dynamic Sql. Yes, I have considered the single where clause, but not sure how to do that.
– Greg Gum
Nov 19 at 12:16

@Larnu, No, I had not considered Dynamic Sql. Yes, I have considered the single where clause, but not sure how to do that.
– Greg Gum
Nov 19 at 12:16

2

2

Peruse this dynamic search conditions article.
– Dan Guzman
Nov 19 at 12:21

Peruse this dynamic search conditions article.
– Dan Guzman
Nov 19 at 12:21

@DanGuzman, that is exactly what I was looking for, thank you.
– Greg Gum
Nov 19 at 12:31

@DanGuzman, that is exactly what I was looking for, thank you.
– Greg Gum
Nov 19 at 12:31

add a comment | 

4 Answers
4

active

oldest

votes

up vote
4
down vote



accepted

You can do:

select i.*

from Items i

where (@dueTypeCode = 'ALL') or

      (@dueTypeCode = 'TODAY' and DueDate = @today) or

      (@dueTypeCode = 'NEXT5DAYS' and DueDate >= @today and DueDate <= @fiveDaysFromNow);

The downside is that this is likely not to be as efficient as the original queries -- if indexes can be used for that query (particularly on DueDate).

share|improve this answer

answered Nov 19 at 12:14

Gordon Linoff

745k32285390

• 
  
  
  

  
  

  
  
  
  
  
  

  
  I don't get why something like this would not be able to use an index (ie be more inefficient.) Would adding the RECOMPILE option be the answer to that?
  – Greg Gum
  Nov 19 at 12:19
  
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  Adding an OPTION(RECOMPILE) hint may provide a better plan but at the cost of query compilation, which not be a concern if the query isn't frequently executed.
  – Dan Guzman
  Nov 19 at 12:22
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  build the query, then look at the query execution plan and/or do some tests.
  – Cato
  Nov 19 at 12:26
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  @GregGum . . . This has to do with parameter sniffing and the fact that SQL Server chooses the execution plan based on the first execution of the query.
  – Gordon Linoff
  Nov 19 at 13:10
  

  
  
  
  

add a comment | 

up vote
1
down vote

Since you're using variables why don't you just modify the variables:

DECLARE @dueTypeCode NVARCHAR(10) = 'NEXT5DAYS';

DECLARE @date1 DATE = NULL;

DECLARE @date2 DATE = NULL;



IF @dueTypeCode = 'TODAY'

BEGIN

    SET @date1 = GETUTCDATE();

    SET @date2 = GETUTCDATE() + 1;

END

ELSE IF @dueTypeCode = 'NEXT5DAYS'

BEGIN

    SET @date1 = GETUTCDATE();

    SET @date2 = GETUTCDATE() + 6;

END



SELECT * FROM Items

WHERE (@date1 IS NULL OR DueDate >= @date1)

AND   (@date2 IS NULL OR DueDate <  @date2)

share|improve this answer

edited Nov 19 at 12:55

answered Nov 19 at 12:49

Salman A

171k65328414

add a comment | 

up vote
1
down vote

My style :

   declare @dueTypeCode nvarchar(3) = 'ALL' --Is actually a parameter that is passed 

   into the stored procedure

   declare @today DateTime = GetUtcDate();

   declare @tomorrow DateTime = dateadd(day,datediff(day,-1,GETUTCDATE()),0);

   declare @fiveDaysFromNow DateTime = dateadd(day,datediff(day,-5,GETUTCDATE()),0);



   Select * 

   from Items

   where 1= case @dueTypeCode 

        when 'ALL' then 1

        when 'TODAY' then

            case when DueDate =  @today then 1 else 0 end

        when 'NEXT5DAYS' then

            case when DueDate  >= @today and DueDate <= @fiveDaysFromNow  then 1 else 0 end

        else

            0

        end

share|improve this answer

answered 2 days ago

CAGDAS AYDIN

414

add a comment | 

up vote
0
down vote

Building off what Gordon posted - you could get optimal performance with Dynamic SQL. Starting with this sample data:

SET NOCOUNT ON;

USE tempdb;

GO



IF OBJECT_ID('dbo.items','U') IS NOT NULL DROP TABLE dbo.items;

GO



SELECT col1    = CAST(NEWID() AS VARCHAR(100)), 

       DueDate = ISNULL(DATEADD(DAY,t.c*2,d.dt),d.dt)

INTO   dbo.items

FROM (VALUES(0),(0),(1),(2),(3),(4)) AS t(c)

CROSS JOIN (VALUES(CAST(GETDATE() AS DATE))) AS d(dt);

GO

CREATE CLUSTERED INDEX cl_nu__dbo_items__DueDate ON dbo.items(DueDate);

GO

Solution:

DECLARE @dueTypeCode VARCHAR(100) = 'TODAY' --'NEXT5DAYS --'ALL';



DECLARE @sql NVARCHAR(4000) = 

N'SELECT i.*

FROM dbo.Items i'+CHAR(10);



SELECT @sql += 

  CASE @dueTypeCode 

    WHEN 'TODAY'     THEN 'WHERE DueDate = CAST(getdate() AS date);'

    WHEN 'NEXT5DAYS' THEN 'WHERE DueDate >= CAST(getdate() AS date) AND DueDate <= DATEADD(DAY,5,CAST(getdate() AS date))'

    ELSE ''

  END;



EXEC sp_executesql @statement = @sql;

Obviously the business logic would need to be updated to match your needs (e.g. Note my lazy use of ELSE '' which handles "ALL" as well as anything else, this will need to be changed). That said, here's the execution plan when choosing "TODAY" or "NEXT5DAYS". An index seek in this situation is basically the best you can hope for.

share|improve this answer

answered 2 days ago

Alan Burstein

3,5931712

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

 

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53374399%2fhow-to-not-repeat-yourself-with-sql-where-clause%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

4 Answers
4

active

oldest

votes

4 Answers
4

active

oldest

votes

active

oldest

votes

active

oldest

votes

up vote
4
down vote



accepted

You can do:

select i.*

from Items i

where (@dueTypeCode = 'ALL') or

      (@dueTypeCode = 'TODAY' and DueDate = @today) or

      (@dueTypeCode = 'NEXT5DAYS' and DueDate >= @today and DueDate <= @fiveDaysFromNow);

The downside is that this is likely not to be as efficient as the original queries -- if indexes can be used for that query (particularly on DueDate).

share|improve this answer

answered Nov 19 at 12:14

Gordon Linoff

745k32285390

• 
  
  
  

  
  

  
  
  
  
  
  

  
  I don't get why something like this would not be able to use an index (ie be more inefficient.) Would adding the RECOMPILE option be the answer to that?
  – Greg Gum
  Nov 19 at 12:19
  
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  Adding an OPTION(RECOMPILE) hint may provide a better plan but at the cost of query compilation, which not be a concern if the query isn't frequently executed.
  – Dan Guzman
  Nov 19 at 12:22
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  build the query, then look at the query execution plan and/or do some tests.
  – Cato
  Nov 19 at 12:26
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  @GregGum . . . This has to do with parameter sniffing and the fact that SQL Server chooses the execution plan based on the first execution of the query.
  – Gordon Linoff
  Nov 19 at 13:10
  

  
  
  
  

add a comment | 

up vote
4
down vote



accepted

You can do:

select i.*

from Items i

where (@dueTypeCode = 'ALL') or

      (@dueTypeCode = 'TODAY' and DueDate = @today) or

      (@dueTypeCode = 'NEXT5DAYS' and DueDate >= @today and DueDate <= @fiveDaysFromNow);

The downside is that this is likely not to be as efficient as the original queries -- if indexes can be used for that query (particularly on DueDate).

share|improve this answer

answered Nov 19 at 12:14

Gordon Linoff

745k32285390

• 
  
  
  

  
  

  
  
  
  
  
  

  
  I don't get why something like this would not be able to use an index (ie be more inefficient.) Would adding the RECOMPILE option be the answer to that?
  – Greg Gum
  Nov 19 at 12:19
  
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  Adding an OPTION(RECOMPILE) hint may provide a better plan but at the cost of query compilation, which not be a concern if the query isn't frequently executed.
  – Dan Guzman
  Nov 19 at 12:22
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  build the query, then look at the query execution plan and/or do some tests.
  – Cato
  Nov 19 at 12:26
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  @GregGum . . . This has to do with parameter sniffing and the fact that SQL Server chooses the execution plan based on the first execution of the query.
  – Gordon Linoff
  Nov 19 at 13:10
  

  
  
  
  

add a comment | 

up vote
4
down vote



accepted

up vote
4
down vote



accepted

You can do:

select i.*

from Items i

where (@dueTypeCode = 'ALL') or

      (@dueTypeCode = 'TODAY' and DueDate = @today) or

      (@dueTypeCode = 'NEXT5DAYS' and DueDate >= @today and DueDate <= @fiveDaysFromNow);

The downside is that this is likely not to be as efficient as the original queries -- if indexes can be used for that query (particularly on DueDate).

share|improve this answer

answered Nov 19 at 12:14

Gordon Linoff

745k32285390

You can do:

select i.*

from Items i

where (@dueTypeCode = 'ALL') or

      (@dueTypeCode = 'TODAY' and DueDate = @today) or

      (@dueTypeCode = 'NEXT5DAYS' and DueDate >= @today and DueDate <= @fiveDaysFromNow);

The downside is that this is likely not to be as efficient as the original queries -- if indexes can be used for that query (particularly on DueDate).

share|improve this answer

answered Nov 19 at 12:14

Gordon Linoff

745k32285390

share|improve this answer

share|improve this answer

answered Nov 19 at 12:14

Gordon Linoff

745k32285390

answered Nov 19 at 12:14

Gordon Linoff

745k32285390

answered Nov 19 at 12:14

Gordon Linoff

745k32285390

745k32285390

• 
  
  
  

  
  

  
  
  
  
  
  

  
  I don't get why something like this would not be able to use an index (ie be more inefficient.) Would adding the RECOMPILE option be the answer to that?
  – Greg Gum
  Nov 19 at 12:19
  
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  Adding an OPTION(RECOMPILE) hint may provide a better plan but at the cost of query compilation, which not be a concern if the query isn't frequently executed.
  – Dan Guzman
  Nov 19 at 12:22
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  build the query, then look at the query execution plan and/or do some tests.
  – Cato
  Nov 19 at 12:26
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  @GregGum . . . This has to do with parameter sniffing and the fact that SQL Server chooses the execution plan based on the first execution of the query.
  – Gordon Linoff
  Nov 19 at 13:10
  

  
  
  
  

add a comment | 

• 
  
  
  

  
  

  
  
  
  
  
  

  
  I don't get why something like this would not be able to use an index (ie be more inefficient.) Would adding the RECOMPILE option be the answer to that?
  – Greg Gum
  Nov 19 at 12:19
  
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  Adding an OPTION(RECOMPILE) hint may provide a better plan but at the cost of query compilation, which not be a concern if the query isn't frequently executed.
  – Dan Guzman
  Nov 19 at 12:22
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  build the query, then look at the query execution plan and/or do some tests.
  – Cato
  Nov 19 at 12:26
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  @GregGum . . . This has to do with parameter sniffing and the fact that SQL Server chooses the execution plan based on the first execution of the query.
  – Gordon Linoff
  Nov 19 at 13:10
  

  
  
  
  

I don't get why something like this would not be able to use an index (ie be more inefficient.) Would adding the RECOMPILE option be the answer to that?
– Greg Gum
Nov 19 at 12:19

I don't get why something like this would not be able to use an index (ie be more inefficient.) Would adding the RECOMPILE option be the answer to that?
– Greg Gum
Nov 19 at 12:19

1

1

Adding an OPTION(RECOMPILE) hint may provide a better plan but at the cost of query compilation, which not be a concern if the query isn't frequently executed.
– Dan Guzman
Nov 19 at 12:22

Adding an OPTION(RECOMPILE) hint may provide a better plan but at the cost of query compilation, which not be a concern if the query isn't frequently executed.
– Dan Guzman
Nov 19 at 12:22

build the query, then look at the query execution plan and/or do some tests.
– Cato
Nov 19 at 12:26

build the query, then look at the query execution plan and/or do some tests.
– Cato
Nov 19 at 12:26

2

2

@GregGum . . . This has to do with parameter sniffing and the fact that SQL Server chooses the execution plan based on the first execution of the query.
– Gordon Linoff
Nov 19 at 13:10

@GregGum . . . This has to do with parameter sniffing and the fact that SQL Server chooses the execution plan based on the first execution of the query.
– Gordon Linoff
Nov 19 at 13:10

add a comment | 

up vote
1
down vote

Since you're using variables why don't you just modify the variables:

DECLARE @dueTypeCode NVARCHAR(10) = 'NEXT5DAYS';

DECLARE @date1 DATE = NULL;

DECLARE @date2 DATE = NULL;



IF @dueTypeCode = 'TODAY'

BEGIN

    SET @date1 = GETUTCDATE();

    SET @date2 = GETUTCDATE() + 1;

END

ELSE IF @dueTypeCode = 'NEXT5DAYS'

BEGIN

    SET @date1 = GETUTCDATE();

    SET @date2 = GETUTCDATE() + 6;

END



SELECT * FROM Items

WHERE (@date1 IS NULL OR DueDate >= @date1)

AND   (@date2 IS NULL OR DueDate <  @date2)

share|improve this answer

edited Nov 19 at 12:55

answered Nov 19 at 12:49

Salman A

171k65328414

add a comment | 

up vote
1
down vote

Since you're using variables why don't you just modify the variables:

DECLARE @dueTypeCode NVARCHAR(10) = 'NEXT5DAYS';

DECLARE @date1 DATE = NULL;

DECLARE @date2 DATE = NULL;



IF @dueTypeCode = 'TODAY'

BEGIN

    SET @date1 = GETUTCDATE();

    SET @date2 = GETUTCDATE() + 1;

END

ELSE IF @dueTypeCode = 'NEXT5DAYS'

BEGIN

    SET @date1 = GETUTCDATE();

    SET @date2 = GETUTCDATE() + 6;

END



SELECT * FROM Items

WHERE (@date1 IS NULL OR DueDate >= @date1)

AND   (@date2 IS NULL OR DueDate <  @date2)

share|improve this answer

edited Nov 19 at 12:55

answered Nov 19 at 12:49

Salman A

171k65328414

add a comment | 

up vote
1
down vote

up vote
1
down vote

Since you're using variables why don't you just modify the variables:

DECLARE @dueTypeCode NVARCHAR(10) = 'NEXT5DAYS';

DECLARE @date1 DATE = NULL;

DECLARE @date2 DATE = NULL;



IF @dueTypeCode = 'TODAY'

BEGIN

    SET @date1 = GETUTCDATE();

    SET @date2 = GETUTCDATE() + 1;

END

ELSE IF @dueTypeCode = 'NEXT5DAYS'

BEGIN

    SET @date1 = GETUTCDATE();

    SET @date2 = GETUTCDATE() + 6;

END



SELECT * FROM Items

WHERE (@date1 IS NULL OR DueDate >= @date1)

AND   (@date2 IS NULL OR DueDate <  @date2)

share|improve this answer

edited Nov 19 at 12:55

answered Nov 19 at 12:49

Salman A

171k65328414

Since you're using variables why don't you just modify the variables:

DECLARE @dueTypeCode NVARCHAR(10) = 'NEXT5DAYS';

DECLARE @date1 DATE = NULL;

DECLARE @date2 DATE = NULL;



IF @dueTypeCode = 'TODAY'

BEGIN

    SET @date1 = GETUTCDATE();

    SET @date2 = GETUTCDATE() + 1;

END

ELSE IF @dueTypeCode = 'NEXT5DAYS'

BEGIN

    SET @date1 = GETUTCDATE();

    SET @date2 = GETUTCDATE() + 6;

END



SELECT * FROM Items

WHERE (@date1 IS NULL OR DueDate >= @date1)

AND   (@date2 IS NULL OR DueDate <  @date2)

share|improve this answer

edited Nov 19 at 12:55

answered Nov 19 at 12:49

Salman A

171k65328414

share|improve this answer

share|improve this answer

edited Nov 19 at 12:55

edited Nov 19 at 12:55

edited Nov 19 at 12:55

answered Nov 19 at 12:49

Salman A

171k65328414

answered Nov 19 at 12:49

Salman A

171k65328414

answered Nov 19 at 12:49

Salman A

171k65328414

171k65328414

add a comment | 

add a comment | 

up vote
1
down vote

My style :

   declare @dueTypeCode nvarchar(3) = 'ALL' --Is actually a parameter that is passed 

   into the stored procedure

   declare @today DateTime = GetUtcDate();

   declare @tomorrow DateTime = dateadd(day,datediff(day,-1,GETUTCDATE()),0);

   declare @fiveDaysFromNow DateTime = dateadd(day,datediff(day,-5,GETUTCDATE()),0);



   Select * 

   from Items

   where 1= case @dueTypeCode 

        when 'ALL' then 1

        when 'TODAY' then

            case when DueDate =  @today then 1 else 0 end

        when 'NEXT5DAYS' then

            case when DueDate  >= @today and DueDate <= @fiveDaysFromNow  then 1 else 0 end

        else

            0

        end

share|improve this answer

answered 2 days ago

CAGDAS AYDIN

414

add a comment | 

up vote
1
down vote

My style :

   declare @dueTypeCode nvarchar(3) = 'ALL' --Is actually a parameter that is passed 

   into the stored procedure

   declare @today DateTime = GetUtcDate();

   declare @tomorrow DateTime = dateadd(day,datediff(day,-1,GETUTCDATE()),0);

   declare @fiveDaysFromNow DateTime = dateadd(day,datediff(day,-5,GETUTCDATE()),0);



   Select * 

   from Items

   where 1= case @dueTypeCode 

        when 'ALL' then 1

        when 'TODAY' then

            case when DueDate =  @today then 1 else 0 end

        when 'NEXT5DAYS' then

            case when DueDate  >= @today and DueDate <= @fiveDaysFromNow  then 1 else 0 end

        else

            0

        end

share|improve this answer

answered 2 days ago

CAGDAS AYDIN

414

add a comment | 

up vote
1
down vote

up vote
1
down vote

My style :

   declare @dueTypeCode nvarchar(3) = 'ALL' --Is actually a parameter that is passed 

   into the stored procedure

   declare @today DateTime = GetUtcDate();

   declare @tomorrow DateTime = dateadd(day,datediff(day,-1,GETUTCDATE()),0);

   declare @fiveDaysFromNow DateTime = dateadd(day,datediff(day,-5,GETUTCDATE()),0);



   Select * 

   from Items

   where 1= case @dueTypeCode 

        when 'ALL' then 1

        when 'TODAY' then

            case when DueDate =  @today then 1 else 0 end

        when 'NEXT5DAYS' then

            case when DueDate  >= @today and DueDate <= @fiveDaysFromNow  then 1 else 0 end

        else

            0

        end

share|improve this answer

answered 2 days ago

CAGDAS AYDIN

414

My style :

   declare @dueTypeCode nvarchar(3) = 'ALL' --Is actually a parameter that is passed 

   into the stored procedure

   declare @today DateTime = GetUtcDate();

   declare @tomorrow DateTime = dateadd(day,datediff(day,-1,GETUTCDATE()),0);

   declare @fiveDaysFromNow DateTime = dateadd(day,datediff(day,-5,GETUTCDATE()),0);



   Select * 

   from Items

   where 1= case @dueTypeCode 

        when 'ALL' then 1

        when 'TODAY' then

            case when DueDate =  @today then 1 else 0 end

        when 'NEXT5DAYS' then

            case when DueDate  >= @today and DueDate <= @fiveDaysFromNow  then 1 else 0 end

        else

            0

        end

share|improve this answer

answered 2 days ago

CAGDAS AYDIN

414

share|improve this answer

share|improve this answer

answered 2 days ago

CAGDAS AYDIN

414

answered 2 days ago

CAGDAS AYDIN

414

answered 2 days ago

CAGDAS AYDIN

414

414

add a comment | 

add a comment | 

up vote
0
down vote

Building off what Gordon posted - you could get optimal performance with Dynamic SQL. Starting with this sample data:

SET NOCOUNT ON;

USE tempdb;

GO



IF OBJECT_ID('dbo.items','U') IS NOT NULL DROP TABLE dbo.items;

GO



SELECT col1    = CAST(NEWID() AS VARCHAR(100)), 

       DueDate = ISNULL(DATEADD(DAY,t.c*2,d.dt),d.dt)

INTO   dbo.items

FROM (VALUES(0),(0),(1),(2),(3),(4)) AS t(c)

CROSS JOIN (VALUES(CAST(GETDATE() AS DATE))) AS d(dt);

GO

CREATE CLUSTERED INDEX cl_nu__dbo_items__DueDate ON dbo.items(DueDate);

GO

Solution:

DECLARE @dueTypeCode VARCHAR(100) = 'TODAY' --'NEXT5DAYS --'ALL';



DECLARE @sql NVARCHAR(4000) = 

N'SELECT i.*

FROM dbo.Items i'+CHAR(10);



SELECT @sql += 

  CASE @dueTypeCode 

    WHEN 'TODAY'     THEN 'WHERE DueDate = CAST(getdate() AS date);'

    WHEN 'NEXT5DAYS' THEN 'WHERE DueDate >= CAST(getdate() AS date) AND DueDate <= DATEADD(DAY,5,CAST(getdate() AS date))'

    ELSE ''

  END;



EXEC sp_executesql @statement = @sql;

Obviously the business logic would need to be updated to match your needs (e.g. Note my lazy use of ELSE '' which handles "ALL" as well as anything else, this will need to be changed). That said, here's the execution plan when choosing "TODAY" or "NEXT5DAYS". An index seek in this situation is basically the best you can hope for.

share|improve this answer

answered 2 days ago

Alan Burstein

3,5931712

add a comment | 

up vote
0
down vote

Building off what Gordon posted - you could get optimal performance with Dynamic SQL. Starting with this sample data:

SET NOCOUNT ON;

USE tempdb;

GO



IF OBJECT_ID('dbo.items','U') IS NOT NULL DROP TABLE dbo.items;

GO



SELECT col1    = CAST(NEWID() AS VARCHAR(100)), 

       DueDate = ISNULL(DATEADD(DAY,t.c*2,d.dt),d.dt)

INTO   dbo.items

FROM (VALUES(0),(0),(1),(2),(3),(4)) AS t(c)

CROSS JOIN (VALUES(CAST(GETDATE() AS DATE))) AS d(dt);

GO

CREATE CLUSTERED INDEX cl_nu__dbo_items__DueDate ON dbo.items(DueDate);

GO

Solution:

DECLARE @dueTypeCode VARCHAR(100) = 'TODAY' --'NEXT5DAYS --'ALL';



DECLARE @sql NVARCHAR(4000) = 

N'SELECT i.*

FROM dbo.Items i'+CHAR(10);



SELECT @sql += 

  CASE @dueTypeCode 

    WHEN 'TODAY'     THEN 'WHERE DueDate = CAST(getdate() AS date);'

    WHEN 'NEXT5DAYS' THEN 'WHERE DueDate >= CAST(getdate() AS date) AND DueDate <= DATEADD(DAY,5,CAST(getdate() AS date))'

    ELSE ''

  END;



EXEC sp_executesql @statement = @sql;

Obviously the business logic would need to be updated to match your needs (e.g. Note my lazy use of ELSE '' which handles "ALL" as well as anything else, this will need to be changed). That said, here's the execution plan when choosing "TODAY" or "NEXT5DAYS". An index seek in this situation is basically the best you can hope for.

share|improve this answer

answered 2 days ago

Alan Burstein

3,5931712

add a comment | 

up vote
0
down vote

up vote
0
down vote

Building off what Gordon posted - you could get optimal performance with Dynamic SQL. Starting with this sample data:

SET NOCOUNT ON;

USE tempdb;

GO



IF OBJECT_ID('dbo.items','U') IS NOT NULL DROP TABLE dbo.items;

GO



SELECT col1    = CAST(NEWID() AS VARCHAR(100)), 

       DueDate = ISNULL(DATEADD(DAY,t.c*2,d.dt),d.dt)

INTO   dbo.items

FROM (VALUES(0),(0),(1),(2),(3),(4)) AS t(c)

CROSS JOIN (VALUES(CAST(GETDATE() AS DATE))) AS d(dt);

GO

CREATE CLUSTERED INDEX cl_nu__dbo_items__DueDate ON dbo.items(DueDate);

GO

Solution:

DECLARE @dueTypeCode VARCHAR(100) = 'TODAY' --'NEXT5DAYS --'ALL';



DECLARE @sql NVARCHAR(4000) = 

N'SELECT i.*

FROM dbo.Items i'+CHAR(10);



SELECT @sql += 

  CASE @dueTypeCode 

    WHEN 'TODAY'     THEN 'WHERE DueDate = CAST(getdate() AS date);'

    WHEN 'NEXT5DAYS' THEN 'WHERE DueDate >= CAST(getdate() AS date) AND DueDate <= DATEADD(DAY,5,CAST(getdate() AS date))'

    ELSE ''

  END;



EXEC sp_executesql @statement = @sql;

Obviously the business logic would need to be updated to match your needs (e.g. Note my lazy use of ELSE '' which handles "ALL" as well as anything else, this will need to be changed). That said, here's the execution plan when choosing "TODAY" or "NEXT5DAYS". An index seek in this situation is basically the best you can hope for.

share|improve this answer

answered 2 days ago

Alan Burstein

3,5931712

Building off what Gordon posted - you could get optimal performance with Dynamic SQL. Starting with this sample data:

SET NOCOUNT ON;

USE tempdb;

GO



IF OBJECT_ID('dbo.items','U') IS NOT NULL DROP TABLE dbo.items;

GO



SELECT col1    = CAST(NEWID() AS VARCHAR(100)), 

       DueDate = ISNULL(DATEADD(DAY,t.c*2,d.dt),d.dt)

INTO   dbo.items

FROM (VALUES(0),(0),(1),(2),(3),(4)) AS t(c)

CROSS JOIN (VALUES(CAST(GETDATE() AS DATE))) AS d(dt);

GO

CREATE CLUSTERED INDEX cl_nu__dbo_items__DueDate ON dbo.items(DueDate);

GO

Solution:

DECLARE @dueTypeCode VARCHAR(100) = 'TODAY' --'NEXT5DAYS --'ALL';



DECLARE @sql NVARCHAR(4000) = 

N'SELECT i.*

FROM dbo.Items i'+CHAR(10);



SELECT @sql += 

  CASE @dueTypeCode 

    WHEN 'TODAY'     THEN 'WHERE DueDate = CAST(getdate() AS date);'

    WHEN 'NEXT5DAYS' THEN 'WHERE DueDate >= CAST(getdate() AS date) AND DueDate <= DATEADD(DAY,5,CAST(getdate() AS date))'

    ELSE ''

  END;



EXEC sp_executesql @statement = @sql;

Obviously the business logic would need to be updated to match your needs (e.g. Note my lazy use of ELSE '' which handles "ALL" as well as anything else, this will need to be changed). That said, here's the execution plan when choosing "TODAY" or "NEXT5DAYS". An index seek in this situation is basically the best you can hope for.

share|improve this answer

answered 2 days ago

Alan Burstein

3,5931712

share|improve this answer

share|improve this answer

answered 2 days ago

Alan Burstein

3,5931712

answered 2 days ago

Alan Burstein

3,5931712

answered 2 days ago

Alan Burstein

3,5931712

3,5931712

add a comment | 

add a comment | 

 

draft saved

draft discarded

 

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53374399%2fhow-to-not-repeat-yourself-with-sql-where-clause%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Name

Name

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Name

Name

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

This page is only for reference, If you need detailed information, please check here