SQL Server – Using DAY/WEEK/MONTH/YEAR Constants in Stored Procedures

procedure-definitionsql serverstored-procedurest-sql

Assume I have a table looks like this:

Scores {
    Id uniqueidentifier,
    ScoredAt datetime,
    Value int
}

Basically I want to create a stored procedure that works similar to this DATEDIFF(DAY, @day, GETDATE()) which can use DAY/WEEK… as parameter. This is what i did:

CREATE PROCEDURE GetHighScores
    @scope int --- <<== THIS GUY HERE
AS
BEGIN
    SELECT *
    FROM Honor.Scores
    WHERE DATEDIFF(@scope, Honor.Scores.ScoredAt, GETDATE()) = 0
END
GO

What do I have to put into GetHighScores parameter so that I can do this:

EXEC GetHighScores(MONTH)

As far as I know, MONTH/WEEK/DAY.. those are not actually a value but more of a tricky macro. But that guy DATEDIFF can use it, why couldn't I?

Anyone have any idea to get this done?

Best Answer

This assumes that your week starts on a Sunday, and that you only have data from the past. If you need to collect data for this week/month/year/today and exclude data from the future, you need to calculate a start and end range instead of a single cutoff.

CREATE PROCEDURE dbo.GetHighScores -- always use schema prefix!
  @scope VARCHAR(5) -- why an int?
AS
BEGIN
  SET NOCOUNT ON; -- always use this!

  DECLARE @d DATETIME, @cutoff DATETIME;
  SET @d = DATEDIFF(DAY, 0, GETDATE());

  SET @cutoff = DATEADD(DAY, 1-CASE UPPER (@scope) 
    WHEN 'MONTH' THEN DAY(@d)
    WHEN 'WEEK'  THEN DATEPART(WEEKDAY, @d)
    WHEN 'YEAR'  THEN DATEPART(DAYOFYEAR, @d)
    WHEN 'DAY'   THEN 1 END, @d);

  SELECT columns -- never use SELECT * in production code!
    FROM Honor.Scores
    WHERE ScoredAt >= @cutoff; -- now you have a chance to use an index
END
GO

Some further reading you may find useful:

Related Question