CodeJunky
asked on
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-EC 62E15106D5 ,BC2B3858- CFD1-46E8- 9E6D-F6E50 DE4FADD
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-E C62E15106D 5,BC2B3858 -CFD1-46E8 -9E6D-F6E5 0DE4FADD'
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
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-EC
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-E
SET @ExludeCategory = CHAR(39) + REPLACE(@ExludeCategory,',
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.