Parameter in where clause not working (where <column> in)

HI all,
I'm having an issue that is normally very simple; but I'm missing something here. I'm passing a simple comma dilimited string into a VarChar(MAX) parameter.  I'm then replacing the ',' with ''',''' in order to have the proper syntax to pass into the Where <colomn> not in @parameter.  

So I have a string:
AA660304-BE86-4C85-ABB6-EC62E15106D5,BC2B3858-CFD1-46E8-9E6D-F6E50DE4FADD

I obviously need to change the commas to include an appostraphe on iether side.
I can see that it does; but the parameter doesn't work.  If I do a select on the paramter after passed in I see that the syntax is correct; and when I use the resulting value of the parameter after the REPLACE FUNCTION, by copy/pasting it into the where clause it works.  

DECLARE @ExludeCategory VarChar(MAX) = 'AA660304-BE86-4C85-ABB6-EC62E15106D5,BC2B3858-CFD1-46E8-9E6D-F6E50DE4FADD'

SET @ExludeCategory = CHAR(39) + REPLACE(@ExludeCategory,',',''',''') + CHAR(39)
SELECT
      P.SADDR1            [Name]
      ,p.HMY
      
      ,SUM(CASE WHEN (W.DATE_Completion IS NULL) Then 1 ELSE 0 END) AS [Total Open]
            
FROM ORDERED W
      INNER JOIN PERSON P ON P.HMY = W.PHMY
      
WHERE (W.DATE_COMPLETION IS NULL)
      AND ((SELECT COUNT(*) FROM CATEGORY_ASSOC CA
                  WHERE (CA.WID = W.WID) AND (CA.CID NOT IN(@ExludeCategory))) > 0 )
      
GROUP BY P.SADDR1, P.HMY
ORDER BY P.SADDR1
LVL 4
Thunder724Asked:
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.

Lee SavidgeCommented:
You can't do that. You must use dynamic SQL.
0
Scott PletcherSenior DBACommented:
SQL will always consider the variable a single value in constructs like this:
CA.CID NOT IN(@ExludeCategory)

Use a splitter function to load the separate values into a table, then do a NOT EXISTS lookup on that table.
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
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.