Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2012
    Posts
    188

    Unanswered: Run Stored Procedure Based on Day

    I know you can use
    Code:
    SELECT datename(dw,getdate())
    To get the day of the week. My question now is can you take that a step further and say something like
    Code:
    If datename(dw,getdate()) = 'Monday' Then
    exec Monday_Procedure
    Else
    If datename(dw,getdate()) = 'Tuesday' Then
    exec Tuesday_Procedure
    End If
    End If
    I can't seem to get it to work in SQL Server, and from my googling I haven't found any supporting documentation that says it is possible either.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This isn't exactly what you requested, but it illustrates the basic idea of what you want:
    Code:
    CREATE PROCEDURE ShowMe
       @pMessage        NVARCHAR(20)
    AS
    
    SELECT @pMessage
    
    RETURN
    GO
    
    DECLARE @DOW		NVARCHAR(20) = DateName(dw, GetDate())
    
    SELECT @DOW
    
    IF      'Monday' = @DOW    EXECUTE ShowMe 'Montag'
    ELSE IF 'Tuesday' = @DOW   EXECUTE ShowMe 'Dienstag'
    ELSE IF 'Wednesday' = @DOW EXECUTE ShowMe 'Mittwoch'
    ELSE IF 'Thursday' = @DOW  EXECUTE ShowMe 'Donnerstag'
    ELSE IF 'Friday' = @DOW    EXECUTE ShowMe 'Freitag'
    ELSE IF 'Saturday' = @DOW  EXECUTE ShowMe 'Samstag'
    ELSE IF 'Sunday' = @DOW    EXECUTE ShowMe 'Sonntag'
    ELSE                       EXECUTE ShowMe 'WTF???'
    
    GO
    DROP PROCEDURE ShowMe
    GO
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Do you mean to schedule a job in SQL Agent?

  4. #4
    Join Date
    Feb 2012
    Posts
    188
    Thank you for the quick response! When I try to run this in SQL Server (I copied and pasted your code) I get several errors saying must declare the variable '@DOW'

    M
    Code:
    Declare @DOW
    NVARCHAR(20) = DateName(dw, GetDate())
    Select @DOW
    Is that not declaring it? Am I missing something?!

  5. #5
    Join Date
    Feb 2012
    Posts
    188
    Quote Originally Posted by MCrowley View Post
    Do you mean to schedule a job in SQL Agent?
    Ideally, I want to change the WHERE piece of my SQL Statement based on the day of the week, but I figured that was a super difficult task to do. So what I did was I just wrote 2 SQL statements, and I need statement1 to execute Mon, Wed, Fri and statement2 to execute Tues, Thurs. But I am calling the USP from a C# user form, so I was wanting to be able to just have SQL know which procedure it was to execute based off of the day.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    A simple example, but I am not sure how long I would want to support it:
    Code:
    select *
    from sys.tables
    where (datename(dw, getdate()) = 'monday' and name like 'sys%')
      or  (datename(dw, getdate()) = 'tuesday' and name like 'abc%')

  7. #7
    Join Date
    Feb 2012
    Posts
    188
    Quote Originally Posted by MCrowley View Post
    A simple example, but I am not sure how long I would want to support it:
    Code:
    select *
    from sys.tables
    where (datename(dw, getdate()) = 'monday' and name like 'sys%')
      or  (datename(dw, getdate()) = 'tuesday' and name like 'abc%')
    Why would you not want to support the code?

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by jo15765 View Post
    ThaWhen I try to run this in SQL Server (I copied and pasted your
    My first guess would be that you are running an older SQL Server version that doesn't support declaring and assigning a value at the same time. That code was written and tested on SQL 2012. I think that the declare and assign feature was introduced in SQL 2008, but I'm not certain.

    Worst case scenario is that you declare as one statement and assign as another. Not a big deal by any means.

    Just as an observation, I'd use another way to determine the day-of-week instead of using DateName(). While that documents things nicely, it makes your code completely dependant on the client's locale settings. Being paranoid, I'd probably use:
    Code:
    SELECT DateDiff(d, @KnownDate, GetDate()) % 7
    since nothing I know of will upset that. Using
    Code:
    SELECT DatePart(dw, GetDate())
    is more common, but it is vulnerable to problems with SET DATEFIRST.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by jo15765 View Post
    Why would you not want to support the code?
    This code will force table scans due to the use of functions. It will perform poorly.

    It is also a bugaboo to detect and manage in "canned" code, and can be considered "DBA-bane" that will cause admins to snarl when you walk past their cubes!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Feb 2012
    Posts
    188
    Quote Originally Posted by Pat Phelan View Post
    My first guess would be that you are running an older SQL Server version that doesn't support declaring and assigning a value at the same time. That code was written and tested on SQL 2012. I think that the declare and assign feature was introduced in SQL 2008, but I'm not certain.
    Being paranoid, I'd probably use:
    Code:
    SELECT DateDiff(d, @KnownDate, GetDate()) % 7
    since nothing I know of will upset that. Using
    Code:
    SELECT DatePart(dw, GetDate())
    is more common, but it is vulnerable to problems with SET DATEFIRST.

    -PatP
    AH I am running SQL Server 2005, behind in the times, I know!

    I will test that code in the morning, my afternoon is booked, but I will def give it a twirl to see if I can get this bad boy up and running!

  11. #11
    Join Date
    Feb 2012
    Posts
    188
    Quote Originally Posted by Pat Phelan View Post
    This code will force table scans due to the use of functions. It will perform poorly.

    It is also a bugaboo to detect and manage in "canned" code, and can be considered "DBA-bane" that will cause admins to snarl when you walk past their cubes!

    -PatP
    Ah, well I was only going to use it when I executed a stored procedure. I just wanted to manipulate the outcome dependent upon what day of the week it was. However, I guess this would still cause the DBA to snarl :P

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    DBAs will snarl at almost anything, so that isn't as big a deal as we'd like people to think.

    When SQL Server encounters code that compares function results to a constant, it will usually cause a table scan if the function expression is marked as "non-determinant" by the SQL Optimizer. Since GetDate() varies every time it is called, this expression is considered to be "non-determinant" even though in most cases that isn't likely to be a problem.

    Use of the OR operator in a WHERE clause also leads to table scans most of the time.

    Your idea of creating two separate SELECT oeprations with different WHERE clauses is a good idea, it will produce faster running code than trying to mangle the whole works into a single SELECT statement.

    I'm a die-hard believer in producing code that works, every time, regardless of my customer's SQL Server settings and of the client application's SQL connection settings whenever I can. My prefernce is to produce code that "just works" whenever I can.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •