Tukukkk

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.

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).

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)

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

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.