My syntax runs the 1st iteration and generates the email as needed, but then it stops. It never actually pulls the 2nd value from employeeStatusTypesToProcess. I have added Debug.Print statements all through my code, but I can not find what the issue is or where the culprit line is that causes it to stop. Can someone assist me?
Declare @employeeType varchar(500), @PT varchar(500),@enp varchar(100),@Fulllocation varchar(4000) Create Table employeeData ( employeeName varchar(100), ) Create Table employeeStatusTypesToProcess ( ID int, ProgramType varchar(500) ) Insert Into employeeStatusTypesToProcess VALUES ('1','On Hold'), ('2','Pending'), ('3','Contract'), ('4','Remote'), ('5','Vendor') set nocount on DECLARE C2 CURSOR FOR SELECT ProgramType FROM employeeStatusTypesToProcess OPEN C2 FETCH NEXT FROM C2 INTO @PT WHILE @@FETCH_STATUS = 0 BEGIN truncate table [dbo].[tbl_OHP] truncate table [dbo].[tbl_CRV] if @employeeType = 'Remote' begin INSERT INTO [employeeData] (ProgramName) SELECT DISTINCT([employeenames]) FROM [employeeinformation] WHERE [employmentstatus] = 'Remote' end if @employeeType = 'Vendor' begin INSERT INTO [employeeData] (ProgramName) SELECT DISTINCT([employeenames]) FROM [employeeinformation] WHERE [employmentstatus] = 'Vendor' End if @employeeType = 'Contract' begin INSERT INTO [employeeData] (ProgramName) SELECT DISTINCT([employeenames]) FROM [employeeinformation] WHERE [employmentstatus] = 'Contract' end if @employeeType = 'Pending' begin INSERT INTO [employeeData] (ProgramName) SELECT DISTINCT([employeenames]) FROM [employeeinformation] WHERE [employmentstatus] = 'Pending' end if @employeeType = 'On Hold' begin begin INSERT INTO [employeeData] (ProgramName) SELECT DISTINCT([employeenames]) FROM [employeeinformation] WHERE [employmentstatus] = 'On Hold' end DECLARE C1 CURSOR FOR SELECT employeename FROM employeeData OPEN C1 FETCH NEXT FROM C1 INTO @emp WHILE @@FETCH_STATUS = 0 BEGIN SET @fulllocation = (SELECT [storename] FROM employeeData WHERE employeename = @emp) --Dynamic SQL Queries Here To Get Multiple Sale Values FETCH NEXT FROM C1 INTO @emp END CLOSE C1 DEALLOCATE C1 --Syntax Here to Generate Email With The Data Above FETCH NEXT FROM C2 INTO @Program END CLOSE C2 DEALLOCATE C2