Loop with Read Only cursor?

Discussion in 'ASP General' started by Ed, Aug 9, 2004.

  1. Ed

    Ed Guest

    Hello,

    I posted a question about looping with Select in a While
    loop, a few days ago. Repliers to my post advised me that
    a Cursor would be much better (thanks all for your
    replies). I found a looping example using cursors on the
    net, but I don't know how to declare a read only cursor.
    Note: my objective is to simulate an array of field names
    for creating an xml doc/string (to use with OpenXML
    function). So I created a small table with 22 field names
    (22 rows). Originally, I had 2 columns - a RowNum col and
    a fldName col. But with the following example using a
    Cursor, it seems that I only need 1 column, the fldName
    col, although this seems like more lines of code than the
    Select method. The cursor example follows. The question
    is "How to declare a read only cursor". I also included
    my Select example - actually the full UDF using Select in
    the While loop - if anyone could advise me between the two
    examples which would be more suited for my situation.

    Cursor Example
    -----------------------------------------------------
    declare @fldName varchar(50)
    declare @RowNum int
    declare fldList cursor for
    select fldName from tblflds
    OPEN fldList
    FETCH NEXT FROM fldList
    INTO @fldName
    set @RowNum = 0
    WHILE @@FETCH_STATUS = 0
    BEGIN
    set @RowNum = @RowNum + 1
    print cast(@RowNum as char(2)) + ' ' + @fldName
    FETCH NEXT FROM fldList
    INTO @fldName
    END
    CLOSE fldList
    DEALLOCATE fldList
    --------------------------------------------------------

    UDF using Select in While loop Example
    ----------------------------------------------------
    CREATE FUNCTION ConvertToXML( @str1 varchar (8000))
    --@str1 contains a list of values from external source
    --UDF will create xml string by adding a fieldname to
    --each value from @str1, comma delimited
    returns varchar (8000)

    as

    begin

    declare @fld varchar(255)
    declare @fldNum int
    declare @rownum int --used to increment Select statement
    declare @xml varchar(8000) --resultant xml string
    declare @val varchar(255) --each value in @str1
    declare @pos1 int --get comma delimeter position of @str1
    declare @pos2 int --get comma delimeter position of @str1

    set @xml = '<ROOT><Worktable '
    set @rownum = 0
    set @pos1 = 1
    select top 1 @fldNum = rownum, @fld = fldName from tblflds
    while @rowNum < 22
    begin
    set @pos2 = charindex(',', @str1, @pos1)
    set @val = substring(@str1, @pos1, @pos2 - @pos1)
    set @xml = @xml + @fld + '="' + @val + '" '
    select top 1 @fldNum = rownum, @fld = fldName from
    tblflds where rownum > @fldnum
    set @rowNum = @rownum + 1
    set @pos1 = @pos2 + 1
    End
    set @xml = substring(@xml, 1, len(@xml))
    set @xml = @xml + '/></ROOT>'

    return @xml

    end
    ----------------------------------------------------------

    This UDF works fine with the Select loop. But if this is
    a kludge and the cursor method would be more
    correct/professional/better habit to be in, please
    advise. I am also open to suggestions if there is an
    easier way to parse @str1 or create @xml.

    Thanks,
    Ed
    Ed, Aug 9, 2004
    #1
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?VG9tYXMgS2VwaWM=?=

    Changing DEFAULT cursor to WAIT cursor in ASP

    =?Utf-8?B?VG9tYXMgS2VwaWM=?=, Apr 5, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    2,313
    Bruce Barker
    Apr 5, 2005
  2. keithb
    Replies:
    2
    Views:
    8,004
    keithb
    Jun 7, 2006
  3. invy
    Replies:
    4
    Views:
    387
    CBFalconer
    Dec 28, 2006
  4. dmaziuk
    Replies:
    3
    Views:
    568
    Chris Gonnerman
    Jan 25, 2011
  5. Isaac Won
    Replies:
    9
    Views:
    365
    Ulrich Eckhardt
    Mar 4, 2013
Loading...

Share This Page