Solved

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

Posted on 2014-09-09
2
164 Views
Last Modified: 2014-09-09
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
Comment
Question by:Thunder724
2 Comments
 
LVL 25

Assisted Solution

by:Lee Savidge
Lee Savidge earned 250 total points
ID: 40312399
You can't do that. You must use dynamic SQL.
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 250 total points
ID: 40312403
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now