1

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?

  • @Ngoc Are are getting the error "Invalid parameter 1 specified for datediff" ? You are passing an INT variable into the DATEDIFF function as its first parameter. It expects the format DATEADD (datepart , number , date )... i.e. not an integer but dd for days or mm for months etc.. – DamagedGoods Jun 18 '14 at 11:19
  • (1) you'll need to use dynamic SQL or separate objects or separate queries or pre-calculate a cutoff, because DATEDIFF() doesn't take variables or expressions for the first argument, only constants. (2) Your where clause completely destroys sargability - even if the query could be satisfied by an index, your application of a function against the column is going to force a full scan. (3) How are you passing the word MONTH into @scope, which is an INT? (4) What version of SQL Server please? – Aaron Bertrand Jun 18 '14 at 12:35
  • @DamagedGoods but even if the parameter were a string, you still can't put a variable into the first argument for DATEADD. – Aaron Bertrand Jun 18 '14 at 13:27
  • @aaron Yep, I was working backwards from inside out his original procedure, posted just that to push Ngoc down the right path – DamagedGoods Jun 18 '14 at 14:49
  • @Ngoc I voted your question back up the scale as obviously your new to the forum and you did bother to come back and format the question after original comments. Please vote aaron's complete answer up when you have enough rep as mine was not a ideal solution, but hopefully pushed you in the right direction – DamagedGoods Jun 18 '14 at 14:52
2

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:

| improve this answer | |
  • Damn, you beat me to it, I was refactoring for MKII to use CASE and remove using variables on both sides of my statement to eliminate my table scans. Some of that pesky, paid work got in the way! – DamagedGoods Jun 18 '14 at 14:45
0

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

| improve this answer | |
0

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.