Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Posts
    11

    Question Unanswered: Problem with Open Cursor contain Variable

    I am passing a parameter into a SQL Server function and the parameter will contain value in the this format '^DOP1^~^TLM^'

    i will replace the value using syntax like this

    Select @ldttmp = Replace(Replace(@ldt, '~' , ','), '^', '''')

    with @ldt contain the string '^DOP1^~^TLM^'

    once the replace is done, i'll do a open cursor

    Declare Data_Loss_Cur Cursor For
    Select ..... From ........
    Where ....... And o.DTID in (@ldttmp) Order By t.Seq, c.Seq
    Open Data_Loss_Cur

    however, I can't get any result with this method, i will always get a null value back. did I do something wrong? Is it the problem with Cursor or the way I do the replace?

    Any help will be greatly appreciated.

  2. #2
    Join Date
    Jun 2003
    Posts
    5
    Put this in your query analyzer. That is essentially what you are doing.

    declare @var varchar(20)
    set @var= '''dop'',''123'',''tlm'''
    if 'dop' in (@var)
    print 'works'
    else
    print @var +' won''t work'

    If it is always two variables in that parameter, I would parse them out with text functions, assign them to variables, and change you select clause to incorporate the variables. If there are a dynamic amount of substrings in the parameter, you will need to come up with something else.

  3. #3
    Join Date
    Feb 2003
    Location
    Germany
    Posts
    53

    Re: Problem with Open Cursor contain Variable

    Hi,

    your select (i.e. cursor) will not work, because the IN-Operator does not use @ldttmp as a lookup-list but will compare the field-value against the @var value.

    try to build up an cmd string for the select :

    declare @cmd varchar(1000)

    select @CMD = 'select ... Where ....... And o.DTID in (' + @ldttmp + ') Order By t.Seq, c.Seq '
    :

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •