Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

sql variable not working. why is this?

Status
Not open for further replies.

karen4201

Programmer
Mar 9, 2006
37
US
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.
 
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
 
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]
 
Yep.

First, create this user defined function:

Code:
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...
Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top