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

1 vote

3 answers

11339 views

sql-server t-sql stored-procedures procedure-definition

                          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?
                        

Asked by Ngoc (123 rep)

Jun 18, 2014, 04:48 AM
Last activity: May 7, 2020, 06:14 PM

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

Related Questions