String statement passed to an "IN" statement 1

[dscoiho]

Using SQL Server 2000

I have the following example that is used multiple times through queries
TERR IN('I01','I02','I03')
Terr is VARCHAR.
I am not sure how to do it or if it is even possible to change the example above to the following

DECLARE @ABC VARCHAR(??)
SET @ABC = ('I01','I02','I03')

TERR = @ABC

Any help or guidance would be appreciated.

 

[PatriciaObreja]

DECLARE @ABC VARCHAR(100)
SET @ABC = '(''I01'',''I02'',''I03'')'

Then transform all the queries in dinamically strings:
DECLARE @SQL NVARCHAR(500)
SET @SQL = 'SELECT * FROM Table WHERE Field IN ' + @ABC

And finally execute the dinamically created string:
EXEC sp_executesql @SQL

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium

 

[ca8msm]

I really wouldn't use the dynamic sql approach - it can cause security and performance implications.

Instead, you can load the values into a temporary table and join to it.


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

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[AlexCuse]

I second mark's vote against dynamic SQL.

Start here: thread183-1389789

Hope it helps,

Alex

[small]----signature below----[/small]
I'm pushing an elephant up the stairs

My Crummy Web Page

 

[SQLSister]

And so you understand why dynamic SQL is a bad thing:

http://www.sommarskog.se/dynamic_sql.html

"NOTHING is more important in a database than integrity." ESquared