quote:

---

Originally posted by Jonny1409

Hello,

I'm really struggling with some syntax and Id like some help please if you have the time.

I have an employee table with several fields including employee number and holiday group in it.

I also have a table with several rows in it relating to a holiday. So for example, it would have holiday date, employee number etc.

I then have a stored procedure which does a load of stuff, once you pass it in a holiday group and a date.

I'd like to write a trigger on the employee table, so that when a person's holiday group is changed, it will query the holidays table to get all dates within there pass them into the stored procedure.
Obviously as the stored procedure only takes a holiday group and 1 date, I'd need this to loop.

Does this make sense ?
If so, I'd appreciate any help you can offer.
If not, please let me know so I can try and explain more.

Thanks.

---

DECLARE @hg VARCHAR(100), @date datetime

DECLARE r1_cursor CURSOR FORWARD_ONLY FOR	
	SELECT h.holidayGroup, h.[date] FROM holidays h
	INNER JOIN Inserted e on e.empID = h.empID
		OPEN r1_cursor
		FETCH NEXT FROM r1_cursor INTO @hg, @date
		
		WHILE @@FETCH_STATUS = 0
			BEGIN
				Execute procedure @hg, @date

			FETCH NEXT FROM r1_cursor INTO @hg, @date
			END
	
		CLOSE r1_cursor 
		DEALLOCATE r1_cursor