Parameter in where clause not working (where <column> in)
Posted on 2014-09-09
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:
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)
,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