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>'+@.vendor_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.Hi
Your TOP 10 clause should limit the number of rows returned to be 10
distinct vendors but you could try something like:
DECLARE @.cnt int
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
SET @.cnt = 0
WHILE @.@.FETCH_STATUS = 0 AND @.cnt < 10
BEGIN
SET @.vendor_feed = CASE @.cnt WHEN 0 THEN
'<g:vendor>'+@.vendor_name+'</g:vendor>'
ELSE '<custom atribute: vendor' + CAST(@.cnt as varchar(2)) +
'>'+@.vendor_name+'</custom atribute: vendor' + CAST(@.cnt as varchar(2)) + '>'
END
INSERT INTO temp_event_feed(xml_data) VALUES (@.vendor_feed)
FETCH NEXT FROM vendor_Cursor INTO @.vendor_name
SET @.cnt = @.cnt + 1
END
CLOSE vendor_Cursor
DEALLOCATE vendor_Cursor
If you want to include '+' characters in the element value change it to:
'<g:vendor>+'+@.vendor_name+'+</g:vendor>'
John
"VJ" wrote:
> 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>'+@.vendor_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.
>