Loop with Read Only cursor?

E

Ed

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
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,743
Messages
2,569,478
Members
44,898
Latest member
BlairH7607

Latest Threads

Top