How to use DAY/WEEK/MONTH/YEAR constant as parameter in stored procedure? 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 --- &lt;&lt;== 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?
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 &gt;= @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