Sub query using IN clause doesn't evaluate 2

[almaragni]

Looking for some help on a issue that's bugging me hardy.

I am trying to create a subquery with values created from text string. ie..

=======================================
declare @templist varchar(100) -- temp var of text strng

SELECT @templist = '''' + REPLACE('100,101,102,103',',',''',''') + ''''
-- create text string of 100,101,102,103

select @templist --- display just for ha hahs

-- now i am trying to select from a salesmen table
-- only those entries coded with one of the values
-- from the text string ... -- so i attempt to
-- setup the IN clause so that it has '101','102',...
-- in it, instead of just 101,102 ... which wont work


select salesperson_code
from salesmantable
where salesperson_code in ('''' + REPLACE('100,101,102,103',',',''',''') + '''' )

-- unfortunately this returns a blank set
-- it appears that the IN () clause is not
-- evaluating properly


-- why i am doing this is becuase the text string
-- is stored somewhere and used else where ... such that
-- the quotation marks must not be there


can you help me determine or hint where I can find
how to get the statemnt to evaluate properl.y

thank yo u

 

[almaragni]

sorry. this is the correct tsql command - that does not work.

select salesperson_code from salesmantable
where salesperson_code in (@templist)

 

[Gooser]

Try to use

char(39)

in place of the tick mark (') it can ease your confusion in whether you are placing a tick or escaping a tick.

Hope this helps,


v/r

Gooser

Why do today
that which may not need to be done tomorrow --me

 

[almaragni]

Gooser,

actually that was a very good idea...
and I took a leap and tried chars 40 and 41 for ( and )

however, the variable again although seemingly proper

@templist evals to ('101','102')

still when place in thd the subsequent select IN clause
returns no results

but i do thank you - as I now know how to include specail characters when needed


my guess is that somehow the IN clause must be syntaxed with a different way or option.

thanks

 

[jbenson001]

what is the datatype of salesperson_code ?

 

[almaragni]

salesperson_code is VARCHAR (8)

thnanks

 

[jbenson001]

Is the string of IDs being passed from a front end application, vb, asp...etc.?

 

[almaragni]

Well in the end the string will be from an ASP applicaiton


however - i haven;t even gotten to that point

this is all being done/tested in SQL query analyzer

thanks

 

[gmmastros]

Take a look at this faq's: faq183-5207



-George

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

 

[jbenson001]

Well, to ease your pain, what I have done, is to pass the string already formatted from the front end in the format, into the SP as a parameter:

'101','102','103' .. etc

Then in the SP ..

   Select
     *
   From
      Table
   Where
     CHARINDEX( "'" + CONVERT( Varchar(8), salesperson_code ) + "'", @templist ) > 0

Jim

 

[almaragni]

SUCCESS ! ! ! !

ok - i believe i can report this as a collaborative victory!

The following code did the trick ...

Select *
From salesmantable
Where CHARINDEX( char(39) + CONVERT( Varchar(8), salesperson_code ) + char(39), @templist ) > 0


so it was a combination of the CHAR and CHARINDEX commands that worked.

also the FAQ did help with some explanation - but the coding was very involved

thank you ALL for your help.

 

[jbenson001]

Glad you got it working

Jim

 

[Gooser]

Woo-hoo.

_{Please make checks payable to "Gooser" in US Funds.}

;-)



v/r

Gooser

Why do today
that which may not need to be done tomorrow --me