Link to home
Start Free TrialLog in
Avatar of Jacque Scott
Jacque ScottFlag for United States of America

asked on

Better way to write this curosor

I don't want to use this cursor because it is not good practice and takes too long to run.  I would like this in a Stored Procedure but I can't think of any way to perform the loop without using a 'While...' loop or a 'Cursor'.  I've been reading about Set-Based and I can't figure out how to complete this task using Set-Based.

BEGIN
		/*First Set the AOL fields to 'N'  */
		UPDATE ContactsQSCHEDULING
		SET QCBDIT = 'N',
			QCCORPLLCLP = 'N',
			QCEATMA = 'N',
			QCIDITORIDITADM = 'N',
			QCNVBDT = 'N',
			QCNVTRST = 'N',
			QCTMA = 'N',
			QCILITADMIN = 'N'
	END
	BEGIN	
	
	DECLARE @ContactID VARCHAR(50)
	DECLARE @AOL VARCHAR(50)

	DECLARE c1 CURSOR FOR 
	--define cursor
    
		/*Get all of the records that need to be UPDATED or ADDED   */           
		SELECT c.contacts, m.AreaOfLaw 
		FROM contacts c 
		JOIN MattersContacts mc ON c.CONTACTS = mc.Contacts
		JOIN matters m ON mc.Matters = m.Matters 
		WHERE c.CONTACTCLASS NOT IN ('InActive', 'Closed')
		AND m.AreaOfLaw IN ('TMA', 'IDIT', 'BDIT', 'NVBDT', 'NVTRST', 'IDIT ADMIN', 'CORP', 'LLC', 'LP', 'ILIT ADMIN', 'EATMA')
		AND (mc.IsMain = 'Y' AND mc.MtoCClass IN ('Primary', 'Client', 'Client-Master') AND m.Status NOT IN ('InActive', 'Closed'))
		AND (m.Status <> 'Inactive' AND m.Status <> 'Closed' AND m.Status <> 'Client-Shred')
		AND (c.Status <> 'Deceased' AND c.Status <> 'Closed')
		AND 1=1
		ORDER BY c.CONTACTS
	
	OPEN c1

	FETCH NEXT FROM C1
	INTO @ContactID, @AOL

		WHILE @@FETCH_STATUS = 0
		BEGIN
			print @ContactID
			PRINT @AOL

			IF EXISTS (SELECT contacts
			FROM ContactsQSCHEDULING
			WHERE contacts = @ContactID)

				BEGIN	
					UPDATE ContactsQSCHEDULING
					SET QCTMA = CASE WHEN @AOL = 'TMA' THEN 'Y' ELSE QCTMA END,
						QCIDITORIDITADM =  CASE WHEN @AOL = 'IDIT' THEN 'Y' ELSE QCIDITORIDITADM END,
						QCCORPLLCLP =  CASE WHEN @AOL = 'CORP' THEN 'Y' ELSE QCCORPLLCLP END
					WHERE contacts = @ContactID
				END
		FETCH NEXT FROM c1
		INTO @ContactID, @AOL
		END 
		
	END

	END
	CLOSE c1
	DEALLOCATE c1

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of PadawanDBA
PadawanDBA

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial