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

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.
>

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.
>