Loop with Read Only cursor?
From: Ed (anonymous_at_discussions.microsoft.com)
Date: 08/09/04
- Next message: Mark Schupp: "Re: Dates! Dates! Dates!"
- Previous message: ziggs: "Re: Access97 DB locks up the asp code at times"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 9 Aug 2004 09:32:06 -0700
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
- Next message: Mark Schupp: "Re: Dates! Dates! Dates!"
- Previous message: ziggs: "Re: Access97 DB locks up the asp code at times"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
