Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 198
  • Last Modified:

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
0
Thunder724
Asked:
Thunder724
2 Solutions
 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now