How to use DAY/WEEK/MONTH/YEAR constant as parameter in stored procedure?

Question

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?

Answer 1

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:

• Bad habits to kick : avoiding the schema prefix
• My stored procedure "best practices" checklist
• Bad habits to kick : mis-handling date / range queries
• Bad habits to kick : using SELECT * / omitting the column list

Answer 2

Create three different procedures - GetMonthHighScores, GetWeekHighScores and GetDayHighScores. Call the appropriate one for the given parameter.

Answer 3

It is not the most elegant and using functions in the WHERE clause like this is not a good idea Performance wise as you get Table Scans, but should get you started ! This will give the max score for the current Day, month or Year

CREATE PROC MaxScore ( @scope CHAR(1) )
AS

 SET NOCOUNT ON

    IF @scope = 'm'
        SELECT  MAX(value)
        FROM    scores
        WHERE   DATEPART(month, scoredat) = DATEPART(month, GETDATE())  
    IF @scope = 'd'
        SELECT  MAX(value)
        FROM    scores
        WHERE   DATEPART(day, scoredat) = DATEPART(day, GETDATE())
    IF @scope = 'y'
        SELECT  MAX(value)
        FROM    scores
        WHERE   DATEPART(year, scoredat) = DATEPART(year, GETDATE())
    ELSE
       RAISERROR('Invalid Input',0,1)

You could do it a number of ways, you may want to check out the following commands with your further requirements (availability depending upon your version)

CASE, EOMONTH, BEGIN TRY CATCH THROW

also look at ALL of the date functions & converting Date formats and have a play, it opens up a whole world to manipulate your queries

DATEADD, DATEPART, DATEDIFF, CONVERT

SQL Server Books Online has all of these parameters and offers the formatting codes for dates too so worth a bookmark