SQL Server Performance

Trigger executing user stored procedure...

Discussion in 'T-SQL Performance Tuning for Developers' started by rocker333, Jul 2, 2009.

  1. rocker333 New Member

    I use a usp that checks a given date...moves it past weekends and it checks the date against a holiday table...moving the date to the next business day. Heres the heart:
    Create Procedure uspBusinessDays @startdate smalldatetime, @counter smallint, @NewDate smalldatetime OUTPUT AS
    WHILE @counter > 0
    BEGIN
    SET @startdate = DATEADD(dd, @counter, @startdate)
    IF DATEPART(dw, @startdate) BETWEEN 2 AND 6) AND NOT EXISTS(SELECT holidaydate FROM HolidayTable WHERE holidaydate = @startdate)
    BEGIN @counter = @counter - 1 END
    END
    SET @NewDate = @startdate
    GO
    This is the procedure call from inside the trigger...the trigger passes the starting date and count to advance to the usp then gets the new date back to use in the table.
    DECLARE @start smalldatetime, @DayCount smallint, @NewDateTime smalldatetime
    SET @start = starting date SET @DayCount = 3
    EXEC uspBusinessDays @startdate = @start, @counter = @DayCount, @NewDate = @NewDateTime OUTPUT
    The trigger then uses @NewDateTime writting it to a table.
    Now I've been using this usp for years with .asp. Now I need to fire a trigger to populate a single date field in a report table...which works great testing with query analyzer. Strange thing is...when the trigger fires...the DATEPART function works but the SELECT holidaydate doesn't! I've torn this apart twenty different ways and I'm exhausted!
    Any ideas would be MUCH appreciated!
    thx, rocker333
  2. Adriaan New Member

    You've left out the interesting bit, which would be the code from the trigger that is calling this usp. Are you using DATETIME columns and variables all around?
  3. rocker333 New Member

    Sorry...I've added those details now.
    And to clear the mud...I've used uspBusinessDays for years without fail. I have webpages and other usp s calling it no problem.
    Now I call it with a trigger...the usp advances through the weekends but doesn't ever match a holiday date.
    [UPDATE!] I've found that by pasting the start date into the holiday table IT MATCHES and the process works! Now just gotta find a way to make sure all the date formats matchup!
    I would appreciate any good tips on keeping my date formats matched!
    Thx, rocker
  4. Adriaan New Member

    Why would you ever worry about date formats?
    -1- Make sure everything is declared as DATETIME.
    -2- Never spell out dates in strings (unless you're writing to a text file).
    -3- Never use CONVERT to remove the time part, but something like this:
    DATEADD(d, 0, DATEDIFF(d, 0, GETDATE())).

Share This Page