T-SQL strange issue

Question

I have a large T-SQL query (I only put a part here because I very sure the rest is working nice):

 WHERE /*
                ******* missing filter *******
                this line should filter the risks by @LoggedInPersonID via role 
                ******************************
          AND */(@PropertyID IS NULL OR p.PropertyID = @PropertyID)
          AND (@PCodePattern IS NULL OR p.PCode LIKE @PCodePattern)
          AND (@ZipCodeIDS IS NULL 
                 OR p.ZipCodeID IN (@ZipCodeIDS))

You notice that @ZipCodeIDS is a list... Well, I populate this from code with some ids. When @ZipCodeIDS contains a SINGLE id it works perfectly but if I try to send multiple ids (eg: "14,15") it gives me error....

Do you have any clue?

Ps: Of course I made @ZIpCodeIDS varchar because we do not have array in T-SQL...

UPDATE: If I directly hardcode in the query: IN (14,11) it perfectly works... so somehow I do not correctly send this parameter from my code for multiple values I think it does not see "," as a separator...

UPDATE 2: Tried to split the lists and put the ids into a table... When zipCodeIDS has only one id the query works perfectly. For multiple ones, not... Do you see something strange on ELSE?

    IF @ZipCodeIDS IS NOT NULL
BEGIN
    IF CHARINDEX(',', @ZipCodeIDS) = 0
    BEGIN
        insert @listofIDS values(@ZipCodeIDS)
    END
    ELSE
    BEGIN
        WHILE CHARINDEX(',', @ZipCodeIDS) != 0
        BEGIN
            insert @listofIDS values(Left(@ZipCodeIDS, PatIndex(',', @ZipCodeIDS) - 1))
            SET @ZipCodeIDS = SUBSTRING(@ZipCodeIDS, CHARINDEX(',',@ZipCodeIDS)+1, LEN(@ZipCodeIDS) - CHARINDEX(',',@ZipCodeIDS))
        END
    END
END

You could just print out the query to see what it actually looks like. — NullUserException, Aug 12 '11 at 21:46

score 3 · Answer 1 · edited May 23 '17 at 12:19

3

Have a look at this article -

http://www.sommarskog.se/arrays-in-sql.html

It discusses a number of ways you could alter your query.

This question may also prove helpful -

Parameterize an SQL IN clause

edited May 23 '17 at 12:19

Community

1
1

answered Aug 12 '11 at 21:49

ipr101

24,096
8
59
61

score 2 · Answer 2 · answered Aug 12 '11 at 21:45

2

You can't use IN like that. It's trying to look for p.ZipCodeID where the zip code is equal to '14, 15', in your example.

Instead, you'll have to compare p.ZipCodeID to each zip code you need, e.g.

... OR p.ZipCodeID=14 OR p.ZipCodeID=15

The alternative (which is less preferable) is to dynamically generate the SQL statement and then use EXEC to run it. The main problem with that, of course, is SQL injection.

answered Aug 12 '11 at 21:45

Michael Todd

16,679
4
49
69

Yeah, but because I have N zip codes id, I cannot use it like this....do you know any other work-around? – Cristian Boariu Aug 12 '11 at 21:46
1

Isn't `p.ZipCodeID=14 OR p.ZipCodeID=15` equivalent to `p.ZipCodeID IN (14, 15)`? – NullUserException Aug 12 '11 at 21:48
I do kindly ask for a link or example on how to do this dynamically as you said... – Cristian Boariu Aug 12 '11 at 21:48
@NullUserException Yes, except that when you use parameters, it converts the parameter to, in this case, a string, so the parser actually sees `p.ZipCodeID IN ('14,15')` which is _not_ the same as the `OR` clause used in my answer. – Michael Todd Aug 12 '11 at 21:49

t-clausen.dk · Accepted Answer · 2011-08-12T23:41:27.227

1

Declare @listofIDS table(zipcodeids varchar(10))

Declare @ZipCodeIDS varchar(100)
set  @ZipCodeIDS= '14,15'

set @ZipCodeIDS+= ','
;with cte as
(
select substring(@ZipCodeIDS, 1, charindex(',', @ZipCodeIDS) - 1) num, charindex(',', @ZipCodeIDS) pos
union all
select substring(@ZipCodeIDS, pos + 1, charindex(',', @ZipCodeIDS, pos +1) - pos - 1) num, charindex(',', @ZipCodeIDS, pos +1)
from cte
where charindex(',', @ZipCodeIDS, pos +1) > 0

)
INSERT @listofIDS 
select num from cte


.... or p.ZipCodeID in (select zipcodeids from @listofIDS) 

--------------------

Try this (remember to set all your variables):

Declare @ZipCodeIDS varchar(100)
set  @ZipCodeIDS= '14,15'

set @ZipCodeIDS+= ','
;with cte as
(
select cast(substring(@ZipCodeIDS, 1, charindex(',', @ZipCodeIDS) - 1) as int) num, charindex(',', @ZipCodeIDS) pos
union all
select cast(substring(@ZipCodeIDS, pos + 1, charindex(',', @ZipCodeIDS, pos +1) - pos - 1) as int) num, charindex(',', @ZipCodeIDS, pos +1)
from cte
where charindex(',', @ZipCodeIDS, pos +1) > 0
)

SELECT /*... rest of your query that was not included in the question. no colon, nor anything else, just your query plus the small alteration i made in the end
...
...*/
WHERE /*
                ******* missing filter *******
                this line should filter the risks by @LoggedInPersonID via role 
                ******************************
          AND */(@PropertyID IS NULL OR p.PropertyID = @PropertyID)
          AND (@PCodePattern IS NULL OR p.PCode LIKE @PCodePattern)
          AND (@ZipCodeIDS IS NULL 
                 OR p.ZipCodeID IN (select num from cte))

edited Aug 12 '11 at 23:41

answered Aug 12 '11 at 21:49

t-clausen.dk

43,517
12
59
92

Seems interesting the solution with the table..but please keep in mind that I can have N ids...is there a way to insert n values? – Cristian Boariu Aug 12 '11 at 22:05
Yes, I made a recusive sql to split your text into several id's and populate the temporary table with them – t-clausen.dk Aug 12 '11 at 22:32
Do you know, is there a way to see this temp table values? Seems that even with your code is not working and I wanted to make sure that it correctly puts splited values into the table... – Cristian Boariu Aug 12 '11 at 22:54
Can you please see my UPDATE 2? This is what I wrote myself and it works for list with one id. but for multiple ids it failes...Do you see a problem? – Cristian Boariu Aug 12 '11 at 23:00
you can test my code here http://data.stackexchange.com/stackoverflow/q/109362/ it works just fine – t-clausen.dk Aug 12 '11 at 23:26
It says: "Error: Incorrect syntax near ')'." at the end.. – Cristian Boariu Aug 12 '11 at 23:49
Your code fails because you changed charindex to patindex. Patindex has another syntax. – t-clausen.dk Aug 12 '11 at 23:50
@CristianBoariu let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/2431/discussion-between-t-clausen-dk-and-cristian-boariu) – t-clausen.dk Aug 12 '11 at 23:50

score 0 · Answer 4 · answered Aug 12 '11 at 21:48

0

Can you do DECLARE @ZipCodeIDS TABLE(id INT NOT NULL PRIMARY KEY) and then join this table?

answered Aug 12 '11 at 21:48

a1ex07

36,826
12
90
103

Well, I already have that table...and I do join of course based on which ids from that table user has selected...using checkboxes.. – Cristian Boariu Aug 12 '11 at 21:49

Michael Christensen · Answer 5 · 2011-08-13T08:39:10.080

 AND (@ZipCodeIDS IS NULL OR p.ZipCodeID IN (select z.ID from @ZipCodeIDS))

You state that SQL has no "array" but everything works in sets. Create a temporary table or a table variable and fill it with your zipcodeIDs

declare @ZipCodeIDs = TABLE ( ID int NOT NULL)

fill @ZipCodeIDs with the ints

then use your code as before

If you are trying to pass an array from your .net code to the databaes you can do this with a user defined table type in SQL server 2008.

T-SQL strange issue

5 Answers5