abt IN operator

• sqlpro

  Hall of Fame

  Points: 3859

  More actions

  February 3, 2004 at 6:47 pm

  #161606

  hi friends

  DECLARE @X VARCHAR(100)

  SET @X='''!2OiC'',''!3zje'''

  SELECT @X

  SELECT * FROM CLIENTGROUP WHERE FK_CLIENTID IN (@X)

  GO

  SELECT * FROM CLIENTGROUP WHERE FK_CLIENTID IN ('!2OiC','!3zje')

  go

  why only 2nd stamt works not 1st one.

  am i missing anything here

  i have stored proc which accepts client id .i want to use same proc even if we specify more than one client id passed.

  how can i change my query?

  Thank you very much

   

• Russel Loski

  SSCertifiable

  Points: 7054

  More actions

  February 3, 2004 at 9:24 pm

  #492699

  The first item is looking for a FK_ClientID that has the form:  '!2OiC','!3zje'

  It treats the variable as one item in that list.  If you had "in (@x, @x1, @x2)" then it would look for either @x or @x1 or @x2.

  What you could do is to create a temp table.  At the bottom is a sample solution.

  Russel Loski, MCSD

  declare @dn varchar(20)

  set @dn = '6349-4, 5750-0'

  create table #t (id varchar(20))

  declare @pos int

  declare @endPos int

  declare @len int

  declare @newString varchar(20)

  set @pos = 1

  set @len = len(@dn)

  while @pos > 0

   begin

    set @endPos = charindex (',', @dn, @pos)

    if @endPos = 0

     begin

      set @newString = ltrim(rtrim(substring(@dn, @pos, @len -@pos + 1)))

      set @pos = 0

     end

    else

     begin

      set @newString = ltrim(rtrim(substring(@dn, @pos, @endPos -@pos)))

      set @pos = @endPos + 1

     end

    if len(@newString) > 0

     begin

      insert into #t (id) values (@newString)

     end   

   end 

  select * from DeclineList

  where DealerNumber in (select id from #t)

  drop table #t

  Russel Loski, MCSE Business Intelligence, Data Platform

• sqlpro

  Hall of Fame

  Points: 3859

  More actions

  February 3, 2004 at 9:28 pm

  #492700

  Thank you very much Russel Loski

  I changed my query like following and its working

  greatly

  DECLARE @X VARCHAR(100),@Y nVARCHAR(1000)

  SET @Y=''

  SET @X='''!2OiC'',''!3zje'''

  SET @Y='SELECT * FROM CLIENTGROUP WHERE FK_CLIENTID IN ('+@X+')'

  SELECT @X,@Y

  EXEC   sp_executesql  @Y

  cheers

  Rajani

• David Burrows

  SSC Guru

  Points: 65111

  More actions

  February 4, 2004 at 6:22 am

  #492741

  Or

  SELECT * FROM CLIENTGROUP 

  WHERE CHARINDEX(''''+FK_CLIENTID+'''',@X) > 0

  nb this will do a scan and not a seek like the IN operator does so check performance

   

  Far away is close at hand in the images of elsewhere.
  Anon.

• sqlpro

  Hall of Fame

  Points: 3859

  More actions

  February 4, 2004 at 12:37 pm

  #492787

  Thank you very much David

  I'll try Idea 🙂