Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2006

    Unanswered: some simple proc help

    hey guys very new to sql server - i can take care of the basic CRUD but,i'm not familiar with some of the syntax to handle stuff right in the proc. i've been making multiple round trips to accomplish what i think i should be able to do based on a condition right when im in the procedure itself. so, please bear with me.

    i have a table with these 5 fields.

    "AltNumb" "AltName" "TimeLastRun" "TimeNextRun" "skdDuration"

    ALT001 FIRST ALERT 2/2/2006 12:42:00 PM 2/2/2006 2:35:00 PM 5
    ALT002 SECOND ALERT 2/2/2006 12:42:00 PM 2/2/2006 2:43:00 PM 10
    ALT003 SECOND ALERT 2/2/2006 12:42:00 PM 2/2/2006 2:48:00 PM 15

    i have a service that will call a procedure that takes the current_timestamp and compares it (datediff) with the column "TimeLastRun" and see's if it's equal with the "SkdDuration" - some are set to 5, 10, 15 minutess etc.

    heres's the short lil proc below:

    CREATE PROCEDURE dbo.Check_Time_Duration

    declare @myDate smalldatetime
    select @myDate = getdate()

    SELECT AlertNumber from time_check2 where DATEDIFF(MINUTE,timeLastRun,@myDate) = Schedule_Duration

    what i'd like to do is if anything matches, is Update the two columns "TimeLastRun" & "TimeNextRun" right on the spot based on the AlertNumber(if that row matches in the above query).

    something like this:

    DECLARE @myDate smalldatetime
    select @myDate = getDate()

    Update time_check2 set timelastrun=@myDate,timenextrun=dateadd(minute,5,@ myDate)where AlertNumber ='ALT001'--this would need to be a variable

    BUT the AlertNumber of course matched in the first select

    Hopefully someone can see what i'm trying to accomplish here and give me some help
    Been on Oracle too long - i feel like a real newbie here
    thanks again


  2. #2
    Join Date
    Jan 2006
    For determine which alert number is up, you can do this
    declare @myDate smalldatetime
    select     @myDate = getdate()
    SELECT AlertNumber 
    from     time_check2 
    where   DATEDIFF(MINUTE, TimeLastRun, @myDate) >= Schedule_Duration
    Note the > sign.

    For updating the TimeLastRun & TimeNextRun :
    update    U
    set    TimeLastRun    = @myDate,
             TimeNextRun    = dateadd(minute, Schedule_Duration, @myDate)
    from   time_check2 U
    where DATEDIFF(MINUTE, TimeLastRun, cast(getdate() as smalldatetime)) >= Schedule_Duration

Posting Permissions

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