HELP with WHILE LOOPs 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.VJ (vishal.sql@.gmail.com) writes:
> 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
A cursor for that?
INSERT temp_event_feed(xml_data)
SELECT DISTINCT TOP 10 <g:vendor>' + vendor_name + '</g:vendor>'
FROM event_feed_view
WHERE vendor_id = @.vendor_id

> 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>
The hour is late, so this is somewhat sketchy. I assume that you are on
SQL 2000, on SQL 2005 this is a little easier.
SELECT CASE WHEN cnt = 1 THEN '<g:vendor> + vendor_name + '</g:vendor>'
ELSE '<custom atribute: vendor' + ltrim(str(cnt - 1)) + '>' +
vendor_name +
'</custom atribute: vendor' + ltrim(str(cnt - 1)) + '>'
END
FROM (
SELECT vendor_name,
cnt = (SELECT COUNT(*)
FROM event_feed_view b
WHERE b.vendor_id = @.vendor_id
AND a.vendor_name >= b.vendor_name)
FROM (SELECT DISTINCT TOP 10 vendor_name
FROM event_feed_view
WHERE vendor_id = @.vendor_id
ORDER BY vendor_name) AS a) c
I added an ORDER BY clause, or else the numbering wouldn't work.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks a lot for your reply
I am doing a cursor coz there is some reason for that. Anyways I will
try to run it without cursor
There is a lot of HTML data which needs to be put into the file and
then send to google for search implementation