Mixing
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 edited 2 days ago
Salman A
171k65328414
asked Nov 19 at 12:12
Greg Gum
9,8431573124
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 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 singleWHEREand include theRECOMPILEoption?
– 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 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 edited 2 days ago
Salman A
171k65328414
asked Nov 19 at 12:12
Greg Gum
9,8431573124
edited 2 days ago
Salman A
171k65328414
asked Nov 19 at 12:12
Greg Gum
9,8431573124
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 singleWHEREand include theRECOMPILEoption?
– 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 singleWHEREand include theRECOMPILEoption?
– 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).
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 theRECOMPILEoption be the answer to that?
– Greg Gum
Nov 19 at 12:19
1
Adding anOPTION(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)
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
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.
answered 2 days ago
Alan Burstein
3,5931712
add a comment |
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).
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 theRECOMPILEoption be the answer to that?
– Greg Gum
Nov 19 at 12:19
1
Adding anOPTION(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).
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 theRECOMPILEoption be the answer to that?
– Greg Gum
Nov 19 at 12:19
1
Adding anOPTION(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).
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).
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
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 theRECOMPILEoption be the answer to that?
– Greg Gum
Nov 19 at 12:19
1
Adding anOPTION(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 theRECOMPILEoption be the answer to that?
– Greg Gum
Nov 19 at 12:19
1
Adding anOPTION(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)
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)
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)
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)
answered Nov 19 at 12:49
Salman A
171k65328414
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
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
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
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
answered 2 days ago
CAGDAS AYDIN
414
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.
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.
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.
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.
answered 2 days ago
Alan Burstein
3,5931712
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
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
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
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
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
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Have you considered using Dynamic SQL or putting all the logic in a single
WHEREand include theRECOMPILEoption?– 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