SQL 2000 - Using Quotes and Commas in parameters in Stored Procedures? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL 2000 - Using Quotes and Commas in parameters in Stored Procedures?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-03-03 : 07:29:16

Mark writes "In SQL 2000 (No SP as far as I am aware!!) How do I convert the following simple query into a Stored Procedure which
accepts a variable length string of customerIDs?

(using NorthWind)

select * from Customers where CustomerID in ('ALFKI','ANATR')

Attempt 1

SET QUOTED_IDENTIFIER ON

declare @test varchar(200)

set @test = "'ALFKI','ANATR'"

select * from Customers where CustomerID in (@test)

SET QUOTED_IDENTIFIER OFF

This generates the following error:

Server: Msg 207, Level 16, State 3, Line 5
Invalid column name ''ALFKI','ANATR''."

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2003-03-03 : 09:17:37

quote:

Mark writes "In SQL 2000 (No SP as far as I am aware!!) How do I convert the following simple query into a Stored Procedure which
accepts a variable length string of customerIDs?

(using NorthWind)

select * from Customers where CustomerID in ('ALFKI','ANATR')

Attempt 1

SET QUOTED_IDENTIFIER ON

declare @test varchar(200)

set @test = "'ALFKI','ANATR'"

select * from Customers where CustomerID in (@test)

SET QUOTED_IDENTIFIER OFF

This generates the following error:

Server: Msg 207, Level 16, State 3, Line 5
Invalid column name ''ALFKI','ANATR''."

I can not get u'r question properly but the error in u'r statement is solved :
select * from Customers where CustomerID in ('ALFKI','ANATR')

declare @test varchar(200)

set @test = 'ALFKI'+','+'ANATR'

select * from Customers where CustomerID in (@test)

please elaborate more on the query.

Expect the UnExpected

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-03 : 10:43:50

Note that you are using the IN operator incorrectly; it requires a list or a table of values, not a single string value that may happen to be comma-seperated.

That is

IN ('A','B')

works fine, but

IN ('A,B')

does not. Note the difference: the first is 2 distinct values for the IN operator to search in, the second is 1 value for the IN operator to search in, that just happens to have a comma in it.

You won't get an error, however, or a warning when using IN with 1 value (though you probably should) so it looks fine and SQL accepts it. YOu just won't get back the values you hoped for.

The solution? YOu need to parse your comma-seperated string into a list of values as a temp table or table variable and use THAT as the argument for the IN operator.

Search this site for CSV and you'll find some solutions.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources