Jacque Scott
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.