time stored procedure

[stephenyenchik]

Hello. I am trying to write a stored procedure that executes at midnight every night and checks an employee's hire date from one table and changes a field on another table if today's date is exactly 5 years greater than the start date and the hire date is after 1996.

Here is the code:

CREATE PROCEDURE [usp_check_hiredate]
AS
DECLARE @idfiveyear [int]
SET @idfiveyear=(SELECT DISTINCT id from t_user where hire_date = DATEADD(yy, -5, GETDATE()) AND YEAR(hire_date)>1996)
UPDATE t_timeoff
SET accrual_rate=4.69 WHERE user_id=@idfiveyear and type='vacation'
GO

It's not doing it. Can anyone help me?

S

 

[mwardle]

You don't need to mess around with Declares for this kind of thing.

Update t_timeoff
Set accrual_rate=4.69 WHERE user_id IN (SELECT DISTINCT id FROM t_user WHERE hire_date = DATEADD(yy, -5, GETDATE()) AND YEAR(hire_date)>1996) AND type='vacation'

 

[jhall156]

I think the "AND YEAR(hire_date)>1996" is redundant because the preceding criteria guarantee that the year of the hire_date will be >= 1997. JHall

 

[Andel]

Your sp only works if your SELECT returns just 1 id. Even though you use DISTINCT, it might return multiple IDs.

Mwardle suggestion is the best way to go.


Andel
andel@barroga.net

http://andyb.8m.com