SQL Server Performance Forum – Threads Archive

HELP WITH WHILE LOOP IN A CURSOR

I have a cursor within a cursor which is like Declare vendor_cursor cursor for
select distinct top 10 vendor_name from event_feed_view
Where vendor_id = @vendor_id
Open vendor_cursor
Fetch Next from vendor_Cursor into @vendor_name WHILE @@FETCH_STATUS = 0
BEGIN
select @vendor_feed = ‘<g:vendor>’[email protected]_name+'</g:vendor>’
insert into temp_event_feed(xml_data) values (@vendor_feed) Fetch Next from vendor_Cursor into @vendor_name
END
Close vendor_Cursor
Deallocate vendor_Cursor
The result I get here is printed in XML which is like
‘<g:vendor>’+SHAWN M+'</g:vendor>’
‘<g:vendor>’+MICHAEL L+'</g:vendor>’
‘<g:vendor>’+DAWN K+'</g:vendor>’
‘<g:vendor>’+LISA S+'</g:vendor>’ and so on till 10 Since this is HTML i need my data to be in this format
‘<g:vendor>’+SHAWN M+'</g:vendor>’
‘<custom atribute: vendor1>’+MICHAEL L+<custom atribute: vendor1>
‘<custom atribute: vendor2>’+DAWN K+<custom atribute: vendor2>
‘<custom atribute: vendor3>’+LISA S+<custom atribute: vendor3> till 10. There can be 10 or less or more vendors in the list
But I want only 10 in my HTML and the format should be like mentions So I need to create a loop like do while count <=10
and create this html i would have to create a case if count = 1
then use this format: <g:vendor>’+SHAWN M+'</g:vendor>’ if count is >1
then use the other format
‘<custom atribute: vendor1>’+MICHAEL L+<custom atribute: vendor1>
and print 1 after vendor if count is 2, print 2 after vendor if count is 3. I hope this would be clear what I am looking for. Need to pout couple of loops in there any suggesstion on this.

---

declare @vendor_feed varchar(4000),
@noint [email protected] = 0
selecttop 10
@vendor_feed = coalesce(@vendor_feed + ‘<custom attribute:vendor’ + convert(varchar(2), @no) + ‘>”’ + vendor_name + ”'<custom attribute:vendor’ + convert(varchar(2), @no) + ‘>’,
‘<g:vendor>”+’ + vendor_name + ‘+”</g:vendor>’) + char(13),
@no= @no + 1
fromevent_feed_view print @vendor_feed
KH

---

I get this error Server: Msg 154, Level 15, State 3, Line 98
variable assignment is not allowed in a cursor declaration.

---

Post the code please.
KH

---

You should be able to copy and paste my codes and runs it. I does not make use of cursor at all. The codes are not meant for you to incorporate into your existing code.
KH

---

]]>