Update based on row value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update based on row value

Author

Topic

Heinz23
Yak Posting Veteran

84 Posts

Posted - 2008-09-11 : 07:02:33

Hi all,

I have a table with 5 columns: Job Id (Just an index), Last Run (Date), Next Run (Date), ScheduleMinutes (integer) and IsActive (Bit). Whenever I call my update-SP it should add the ScheduleMinutes to LastRun and store it as NextRun. This works fine:

	Update dbo.Scheduler
	SET LastRun = @LastRun,
		NextRun = DATEADD(n, ScheduleMinutes, @LastRun)
	WHERE JobID = @JobID

Now I need to set also IsActive: If ScheduleMinutes is 0, IsActive should be set to 0, otherwise it should not be modified. I know how to do IF-Clauses for input parameters, but how do I combine it with a value from a row? I tried it this way but that's wrong:


	IF dbo.Scheduler.ScheduleMinutes = 0
	BEGIN
		Update dbo.Scheduler
		SET LastRun = @LastRun,
			IsActive = 0
		Where JobID = @JobID
	END
	ELSE
	BEGIN
		Update dbo.Scheduler
		SET LastRun = @LastRun,
			NextRun = DATEADD(n, ScheduleMinutes, @LastRun)
		WHERE JobID = @JobID
	END

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-09-11 : 09:55:04

This should work, but you can likly optimize this more by adding some filters (I am not sure the criteria of setting IsActive <> 0)


Update a
SET a.LastRun = @LastRun,
,a.IsActive =  case when a.ScheduleMinutes = 0 then 0 else a.IsActive 
end
,a.NextRun = case when a.ScheduleMinutes = 0 then DATEADD(n, ScheduleMinutes, @LastRun)
 else a.NextRun end
dbo.Scheduler a
Where JobID = @JobID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources