sql variable not working. why is this?

[karen4201]

I have a variable that doesn't work properly, but the query works when I use a string of characters....

DECLARE @nameStr _str varchar(3500)
set @nameStr = 'aaa','bbb','ccc'

this works:
select * from tableA where name in ('aaa','bbb','ccc')

but this does NOT work:
select * from tableA where name in ( @nameStr )


Why does the variable not work?

Thanks in advance.

 

[gmmastros]

Read this faqs faq183-5207

It may seem like a lot, but it's an important technique to learn and isn't that difficult to implement.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

If you go for option 1 then read this also

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

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[karen4201]

Hi,

Thanks for the suggestion. I apologize for not including everything in my code question....here is most of what I truly have in my db function:

set @nameStr = 'aaa,bbb,ccc'
set @case_str = replace(@cases,' ','') -- get rid of spaces
set @case_str = '''' + replace(@case_str,',',''',''') + '''' -- create a variable to hold the seperated values


I think I may try a temp table to store the values, then do something like:
select * from tableA where name in ( select the_name from temp_table ) [i wrote this by hand, so it's sort of pseudo code]

 

[gmmastros]

Yep.

First, create this user defined function:

ALTER Function [dbo].[Split](@CommaDelimitedFieldNames Varchar(8000), @CharToFind Char(1) ) 
Returns @Tbl_FieldNames Table (Position Integer Identity(1,1), FieldName VarChar(100)) As 
Begin 
 Set @CommaDelimitedFieldNames = @CommaDelimitedFieldNames + @CharToFind
 Declare @Pos1 Int
 Declare @pos2 Int
 
 Set @Pos1=1
 Set @Pos2=1
 While @Pos1<Len(@CommaDelimitedFieldNames)
 Begin
 Set @Pos1 = CharIndex(@CharToFind, @CommaDelimitedFieldNames,@Pos1)
 Insert @Tbl_FieldNames Select Cast(Substring(@CommaDelimitedFieldNames,@Pos2,@Pos1-@Pos2) As VarChar(100))
 Set @Pos2=@Pos1+1
 Set @Pos1 = @Pos1+1
 End 
 Return
End

Then, you can use it like this...

Declare @nameStr varchar(8000)

set @nameStr = 'aaa,bbb,ccc'

Declare @Temp Table(Val VarChar(100))

Insert Into @Temp(Val)
Select FieldName From dbo.Split(@nameStr, ',')

Select * 
From   TableA
       Inner join @Temp T On TableA.Field = T.Val

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom