Run 2 Cursors

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	

September 12th, 2015 1:06pm

FETCH NEXT FROM C2 INTO @PT
FETCH NEXT FROM C2 INTO @Program                   

This may be the reason.

I strongly recommend writing cursor loops like this:

DECLARE cur CURSOR STATIC LOCAL FOR
  SELECT ...

OPEN cur

WHILE 1 = 1
BEGIN
   FETCH cur INTO ...
   IF @@fetch_status <> 0
      BREAK

   -- Do work here.
END

DEALLOCATE cur

First, always use a cursor that is STATIC LOCAL. The default is a dynamic cursor, and how they work has never been clear to me. With a static cursor, the result set is copied to a worktable in tempdb.

By having a single FETCH statement, you avoid that the FETCH statements disagree, and having the single FETCH close to the cursor declaration makes it easier to check that those two match.

Apart from that, the beginning of the cursor loop looks funky. Why all these:

   if @employeeType = 'Remote'
      begin
         INSERT INTO [employeeData] (ProgramName)
         SELECT DISTINCT([employeenames])
         FROM [employeeinformation]
         WHERE [employmentstatus] = 'Remote'           
      end

Why not just simply:

   INSERT INTO [employeeData] (ProgramName)
   SELECT DISTINCT([employeenames])
   FROM [employeeinformation]
       WHERE [employmentstatus] = @employeeType

In the snippet you posted there is no code to delete the rows from the previous iteration which may be alright, but in such case I wonder why there is a cursor at all.

Free Windows Admin Tool Kit Click here and download it now
September 12th, 2015 2:01pm

SQL is a declarative language. Our goal is to avoid all procedural code. That means no local variables. That means no loops. That means no IF-then-else control flow. And no cursors. 

When we voted in the cursors in the SQL-89 Standard, we were using the DB2 model, which was the old IBM magnetic tape file commands from the 1950's. SQL-86 was not a full language and you still had to get down to the hardware level to do work. Things have changed in the last 30 years, and you need to catcfh up :)

You allow oversized garbage data. You have no idea what the ISO-11179 rules are. A table has a collective or plural name because it models a set; you model a single record from a file instead. Mag tapes again! The USPS uses 35 characters for the name line in a postal address; but you use 100! Was that careful research or just a large round number? Where is the key? 

Here is a guess at the right way for your fake deck of punch cards:  

CREATE TABLE Selected_Personnel
(employee_name VARCHAR(35) NOT NULL PRIMARY KEY);

Why did you do spool the names onto a scratch tape? Or punch them out as cards? In SQL, this would probably be a derived table from a general Personnel table. 

There is no generic id in RDBMS; identifiers are for particular entities. What you have are fake pointers, fake array subscripts and 1950's tape record number. You also mix up INTEGER and CHAR(n) in the DDL. 

There is no status_type in RDBMS. An attribute can be a <something in particular>_status or a <something in particular>_type to be valid. They are totally different kinds of what ISO calls attribute properties. Have you read a book on data modeling yet? 

You do not process a type; you process an entity. This is an auxiliary table for the encoding. 

CREATE TABLE Employment_Status
(id CHAR(1) NOT NULL PRIMARY KEY,
 program_type CHAR(10) NOT NULL); 

INSERT INTO Employment_Status
VALUES 
('1', 'On Hold'), 
('2', 'Pending'), 
('3', 'Contract'), 
('4', 'Remote'), 
('5', 'Vendor');

But this is still wrong. A status is a state of being, so it has a temporal dimension. And it has a state transition rule! Please read:
https://www.simple-talk.com/sql/t-sql-programming/state-transition-constraints/

Your "TRUNCATE TABLE dbo.tbl_OHP;"

is really a dismount tape command in a bad disguise. But even funnier, is the tbl- prefix. This is called a tibble in RDBMS slang (read Phil Factor's columns on this code smell). In the 1950's, the early operating systems allowed only short names (6 to 8 characters) and you had to tell the compiler what the object was in the name. Ask an older programmer about the @ and # prefixes in T-SQL's one-pass compiler. Or the use of I thru N in FORTRAN variable names, if they are really old. 

In a properly designed schema, it is very rare to use SELECT DISTINCT. The uniqueness is enforced by DRI. 

I would have guessed that we would see a derived table in the INSERT INTO statement like this: 

(SELECT DISTINCT employee_name -- assumes an emplyee can have several employment_statuses 
   FROM Personnel
  WHERE employment_status 
IN ('Remote', 'Vendor', 'Contract', 
    'Pending', 'On Hold')) 
AS Candidate_Employees

Since we have no DDL, as required by forum rules, I cannot get any further than this guess. The IN() predicate did not exist in AutoCoder, FORTRAN or COBOL; it first appears in Pascal. That would account for our use of procedural sequenced IF-statements. 

The heuristic is that every cursor decreases performance by an order of magnitude. If you will rad the forum rules and post DDL, we might be able to give you some real help instead of kludges. 

September 12th, 2015 5:07pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics