IN eval

[JCV]

Hi, I need eval a variable value into an "in" operator

As example :

declare @strState varchar(10)
@strState = 'A' + ',' + 'C'

select * from table
where state in (@strState)

This not return nothing.

Please, help me...

 

[tlbroadbent]

When you code the SQL coojmand as you have done, the variable is treated as one item in the list. So what you are asking with that query is "select any rows that contain the string 'A,C' in the columns state." In order to use a variable you will need to use dynamic SQL. See faq183-3132.

See thread183-483640 for another method, which avoids dynamic SQL. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[ClaireHCM]

Try this

declare @strState varchar(20)
set @strState ="'"+ 'A'+"'" + ',' +"'"+'C'+"'"

select @strstate
select * from table
where state in (@strState)

Claire

 

[tlbroadbent]

Claire,

Same problem occurs with your solution. You just made the string longer "'A','C'". If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[ClaireHCM]

tlbroadbent IS RIGHT.

Here you go.

declare @strState varchar(10)
set @strState = '"CA"' + ',' + '"MA"'
print @strstate
DECLARE @QUERY VARCHAR(1000)
SET @QUERY = "select * from AUTHORS where STATE in ("+@strState+&quot"
EXEC (@QUERY)

 

[tlbroadbent]

Claire,

I've already directed JCV to a FAQ that explains the dynamic SQL method you posted. There is no need to post code that already exists in a FAQ and dozens of other threads. If you haven't already read them, I recommend reading the two FAQs listed in my signature at the bottom of this post. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm