query does not show result via procedure

Question

im working with a stored procedure in sql server 2005 DB, procedure has a piece of query that is

.
.
.


if(@zipCodeList <> '')
BEGIN
    SET @zipCodeList = ''''+replace(replace(replace(@zipCodeList, ' ', ''),',',''','''),'_',' ')+''''
END 

SELECT distinct ZIPCode
from zip_table
where ZIPCode_col  IN (@zipCodeList)
AND power((69.1 * (abs(Longitude) - abs(Longitude)) * cos(abs(Latitude)/57.3)),2) +
Power(69.1 * (abs(Latitude) - abs(Latitude)), 2) <= (60 * 60)
AND more conditions 
.
.
.

calling the procedure like

exec proc '02124, 23568'

DB has records, but procedure does not show anything, i printed the query and it prints

SELECT distinct ZIPCode
from zip_table
where ZIPCode_col  IN ('02124','23568')
AND power((69.1 * (abs(Longitude) - abs(Longitude)) * cos(abs(Latitude)/57.3)),2) +
Power(69.1 * (abs(Latitude) - abs(Latitude)), 2) <= (60 * 60)
AND more conditions

and when i run this query this returns the correct result. looking like @zipCodeList is causing some problem at run time but does not gives any error, can any one please help me where im wrong.

Thanks in advance.

`IN (@zipCodeList)` is an invalid construction, you cannot pass a list of values in a single string variable. If you cannot pass in individual values and create a table variable you need to split the values out to a set, see: [Split function equivalent in T-SQL?](http://stackoverflow.com/questions/697519/split-function-equivalent-in-t-sql) — Alex K., Jan 12 '15 at 11:24

score 0 · Accepted Answer · answered Jan 12 '15 at 11:27

If your @zipCodeList has the values like 1,2,3, then execute the following code

SELECT distinct ZIPCode
from zip_table
where ZIPCode_col  IN 
(
    SELECT PARSENAME(REPLACE(Split.a.value('.', 'VARCHAR(500)'),'-','.'),1) 'Ids' 
    FROM  
    (
         SELECT CAST ('<M>' + REPLACE(@zipCodeList, ',', '</M><M>') + '</M>' AS XML) AS Data       
    ) AS A 
    CROSS APPLY Data.nodes ('/M') AS Split(a)
)
AND power((69.1 * (abs(Longitude) - abs(Longitude)) * cos(abs(Latitude)/57.3)),2) +
Power(69.1 * (abs(Latitude) - abs(Latitude)), 2) <= (60 * 60)
AND more conditions

query does not show result via procedure

1 Answers1