Writing a Stored Procedure

I am looking for the best way to approach this problem.

Based on the results of m.AreaOfLaw I need to UPDATE or INSERT into ContactsQSCHEDULING.  

This SELECT statement will return MANY results.  This will be run every night.
SELECT c.contacts as @ContactID, m.AreaOfLaw as @AOL
		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')
		ORDER BY c.CONTACTS

Open in new window


UPDATE ContactsQSCHEDULING
SET QCTMA = CASE WHEN @AOL = 'TMA' THEN 'Y' END,
	QCIDITORIDITADM =  CASE WHEN @AOL = 'IDIT' THEN 'Y' END,
	QCCORPLLCLP =  CASE WHEN @AOL = 'CORP' THEN 'Y' END
WHERE contacts = @ContactID

Open in new window


How should I be writing this?

Thanks for you help.
huerita37Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Jim HornMicrosoft SQL Server Data DudeCommented:
Give us a data mockup, both before and after, of what you're trying to pull off here.
huerita37Author Commented:
SELECT c.contacts as @ContactID, m.AreaOfLaw as @AOL
		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')
		ORDER BY c.CONTACTS                                

Open in new window


Returns the following:

@ContactID                              @AOL
    ' 503Bxyz'                                        'TMA'
   '6672E1d'                                         'IDIT'
   '6672E1d'                                         'TMA'
   '8765hui'                                          'CORP'
   67yh5rT'                                           'TMA'


In the ContactsQSCHEDULING table we look for @ContactID and update a few fields based on the @AOL.  If there is nothing in the ContactsQSCHEDULING table then we INSERT.


ContactsQSCHEDULING Table

ContactID         QCTMA            QCIDIT                QCCORPLLCLP
  ' 503Bxyz'            'Y'                     'N'                                 'N'
 '6672E1d'             'Y'                      'Y'                                 'N'
 '8765hui'               'N'                     'N'                               'Y'
   67yh5rT'              'Y'                     'N'                                'N'



Is this what you meant?
huerita37Author Commented:
Here is the cursor I have written, but I know I need to do it a better way.

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

Vitor MontalvãoMSSQL Senior EngineerCommented:
I would do it in 2 steps.
Firs step with a common table expression to update the records and second step a single insert command for the records that doesn't exists in ContactsQSCHEDULING:
;WITH CTE_Update AS 
	(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'))

UPDATE ContactsQSCHEDULING
SET ContactsQSCHEDULING.QCTMA = CASE 
				WHEN CTE_Update.AreaOfLaw = 'TMA' THEN 'Y' 
				ELSE 'N'
				END,
	ContactsQSCHEDULING.QCIDITORIDITADM =  CASE 
							WHEN CTE_Update.AreaOfLaw = 'IDIT' THEN 'Y' 
							ELSE 'N'
							END,
	ContactsQSCHEDULING.QCCORPLLCLP =  CASE 
						WHEN CTE_Update.AreaOfLaw = 'CORP' THEN 'Y' 
						ELSE 'N'
						END
FROM CTE_Update
WHERE ContactsQSCHEDULING.contacts = CTE_Update.contacts


INSERT INTO ContactsQSCHEDULING (ContactID, QCTMA, QCIDIT, QCCORPLLCLP)
SELECT c.contacts, 
	CASE 
		WHEN m.AreaOfLaw = 'TMA' THEN 'Y' 
		ELSE 'N'
	END, 
	CASE 
		WHEN m.AreaOfLaw = 'IDIT' THEN 'Y' 
		ELSE 'N'
	END, 
	CASE 
		WHEN m.AreaOfLaw = 'CORP' THEN 'Y' 
		ELSE 'N'
	END
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 c.contacts NOT EXISTS (SELECT 1 
				FROM ContactsQSCHEDULING C2 
				WHERE C2.contacts = c.contacts)

Open in new window

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.