Passing a Comma-Delimited String to Stored Procedure

[Paully1999]

I am trying to set up a stored procedure that accepts a comma-delimited string and displays all of the records that their zip code located within that string.

I have

CREATE PROCEDURE dbo_GetContractors
@WorkType varchar(1),
@Zippy nText

AS
SELECT Zip, Search_Id, State, City, Company_Name, WorkType1, banner, bold FROM ContractorsOnly WHERE WorkType1 = @WorkType AND Zip IN @Zippy ORDER BY Company_Name
GO

I get a syntax error by the IN @Zippy.

I expect the string to look like this...

('07002','07010','07022','07032','07047','07071','07073','07105','07657','07660','10011','10014','10016','10022','10028','10031','10169','10301','10306','10454','10461','11101','11103','11105','11106','11211','11217','11218','11222','11228','11229','11232','11238','11373','11377','11378','11385','11417','11419','11421')

 

[tlbroadbent]

You'll need to create and execute a dynamic SQL statement in the SP.

CREATE PROCEDURE dbo_GetContractors
@WorkType varchar(1),
@Zippy nvarchar(4000)
/* I recommend nVarchar instead of nText */

AS

Declare @sql nvarchar(4000)
SET quoted_identifer off

SET @sql=
"SELECT Zip, Search_Id, State, City,
Company_Name, WorkType1, banner, bold
FROM ContractorsOnly
WHERE WorkType1 = " +
@WorkType +
" AND Zip IN " +
@Zippy +
" ORDER BY Company_Name"

exec sp_executesql @sql

GO

NOTE: The line feeds and other formatting simply enhance readability. They don't affect the way the T-SQL runs. Terry

http://members.home.net/tlbroadbent/prog.htm

"I'm not dumb. I just have a command of throughly useless information." - Calvin, of Calvin and Hobbes

 

[Paully1999]

I expect, however, that @Zippy might contain as many as 5000

Zip codes. Still recommend @Zippy nvarchar(some integer).


Can you explain the SET quoted_identifer off is that really the meat of what I needed.

 

[tlbroadbent]

When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers.

This was not the key change recommended. The key change was to create the dynamic SQL statement so the values of the variables could be interpreted. You can't use a variable for the IN clause in a straight select statement.

Use nText if you will be passing that many zip codes. However, you'll have to use more than one variable for building the SQL string. SQL Server will not allow you to declare a ntext local variable. You'll need multiple nvarchar variables.

I'm not sure if SQL will choke on 5000 items in the IN list. You will see poor performance. From a database and application design point of view, the zip codes should be in a table and you should match to the table rather than search an IN clause. Terry

http://members.home.net/tlbroadbent/prog.htm

"I'm not dumb. I just have a command of throughly useless information." - Calvin, of Calvin and Hobbes