some simple proc help

3 Feb 2006 1:08 PM

rbutch@coair.com

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
AS

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'

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

thanks again - working so Long in Oracle - I'm definately a newbie on sql server

rik

**********************************************************************
Sent via Fuzzy Software @ http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

3 Feb 2006 2:40 PM

markc600

If I understand you requirements correctly
you've pretty much solved it already.

CREATE PROCEDURE dbo.Check_Time_Duration
AS

UPDATE time_check2
SET TimeLastRun=CURRENT_TIMESTAMP,
TimeNextRun=DATEADD(minute,5,CURRENT_TIMESTAMP)
WHERE DATEDIFF(minute,TimeLastRun,CURRENT_TIMESTAMP) = skdDuration

3 Feb 2006 6:28 PM

rbutch@coair.com

So, i would just need to run this update or call this from within the other Stored Proc?
thanks again for the help
rik

**********************************************************************
Sent via Fuzzy Software @ http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

Thread options