Multiple Zip Search

I'm heaving a heck of a time to get a sql query working. I'm being passed a series of zip codes and I need to return a list of all the rows that match those zip codes in a database of about 40,000 leads.

Can someone clue me in to what the commandtext of my query should look like?

Thanks!

EXEC( 'SELECT * FROM YourTable where YourZip IN (''' + @.z + ''')' )

where @.z = list of zip codes.

hth|||Well if you are inclined to avoid D-SQL and are using SQL Server 2000, you can use a UDF
or many other techniques which would prove much better than D-SQL.|||My problem is that I'm a web guy with just enough SQL 2000 experience to be dangerous. I'm not exactly sure that the first answer given would help me because the zip codes are different for every query. I have a list of zip codes and I have a parameter set up in my dataadapter (@.zip), but I can't get any results. It's probably an issue with single or double quotes, but i'm not sure. I'm putting all the zip codes together into a session variable, trying to get something like this:

WHERE ZIP LIKE '%80000%' OR ZIP LIKE '%72322%' etc. There can be up to 75 zips in one query.

I really appreciate the help!|||A user defined function to create an query with IN ('zip,zip,etc') is your best bet.

Here is someting I wrote a long time ago but it was similar to what you could do.

IF EXISTS (SELECT 1 FROM sysobjects WHERE name = N'fnConvertListToStringTable')
DROP FUNCTION [dbo].[fnConvertListToStringTable]
GO
CREATE FUNCTION [dbo].[fnConvertListToStringTable] (
@.list varchar(1000)
)
RETURNS @.ListTable table (ItemID varchar(250) NULL)
AS
BEGIN
IF(SUBSTRING(@.list,LEN(@.list),1) <> ',')
BEGIN
SET @.list = @.list + ','
END
DECLARE @.StartLocation int
DECLARE@.Length int
SET @.StartLocation = 0
SET @.Length = 0
SELECT @.StartLocation = CHARINDEX(',',@.list,@.StartLocation+1)
WHILE @.StartLocation > 0
BEGIN
IF(LOWER(RTRIM(LTRIM(SUBSTRING(@.list,@.Length+1,(@.StartLocation-@.length) -1))))) = 'null'
INSERT INTO @.ListTable(ItemID) VALUES (Null)
ELSE
BEGIN
INSERT INTO @.ListTable(ItemID)
VALUES (RTRIM(LTRIM(SUBSTRING(@.list,@.Length+1,(@.StartLocation-@.length) -1))))
END
SET @.Length = @.StartLocation
SET @.StartLocation = CHARINDEX(',', @.list, @.StartLocation +1)
END
RETURN
END
GO
select * from dbo.fnConvertListToStringTable(' yjyj, 1345, 1234,NUll')

The code above actually put the values in a table and then used a where in (Select from that table) but in your case you can juse use the part that does the parsing at the beginning.sql