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

huerita37Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PadawanDBAOperational DBACommented:
Extremely rough pass, but try something like this (on a test environment first, I didn't really have a method to test this out on example data):

UPDATE ContactsQSCHEDULING
SET QCBDIT = 'N',
	QCCORPLLCLP = 'N',
	QCEATMA = 'N',
	QCIDITORIDITADM = 'N',
	QCNVBDT = 'N',
	QCNVTRST = 'N',
	QCTMA = 'N',
	QCILITADMIN = 'N';


update CQS
	set
		QCTMA = CASE WHEN m.areaOfLaw = 'TMA' THEN 'Y' ELSE CQS.QCTMA END,
		QCIDITORIDITADM =  CASE WHEN m.areaOfLaw = 'IDIT' THEN 'Y' ELSE CQS.QCIDITORIDITADM END,
		QCCORPLLCLP =  CASE WHEN m.areaOfLaw = 'CORP' THEN 'Y' ELSE CQS.QCCORPLLCLP END
	FROM 
		contacts as c 
			inner JOIN MattersContacts as mc ON c.CONTACTS = mc.Contacts
			inner JOIN matters as m ON mc.Matters = m.Matters
			inner join ContactsQScheduling as CQS on c.contacts = CQS.contacts
	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;

Open in new window


The landscape: eliminate the cursor, go set-based.  When you are thinking about set-based operations, it helps to think about it in the manner of, how to obtain the data that I am interested in.  Once you, for lack of a better term, define your set, you can perform operations on it.  So in the case above, you are using your table joins and filters based on those tables to determine your set.  And then in the update portion, you are essentially saying "For my data set, I want you to do this." And it will make it so.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.