WHILE LOOP HELP | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

WHILE LOOP HELP

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.
IF I USE THE SOLUTION MENTIONED BELOW WHICH DOES NOT USES CURSOR OR DOES NOT MENTIONS ANY COUNT to 10 (not sure if top 10 would do that), HOW CAN I PROPERLY IMPLEMENT IT. NEED TO DEBUG IT SO IT WORKS PROPERLY:
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

Why re-post the question as a new thread ? instead of continuing over herehttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=14563
KH
Dont Duplicate the post Madhivanan Failing to plan is Planning to fail
]]>