SQL Server Performance

WHILE LOOP HELP

Discussion in 'General Developer Questions' started by vishalj, May 23, 2006.

  1. vishalj New Member

    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.


    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


  2. khtan New Member

  3. Madhivanan Moderator

    Dont Duplicate the post

    Madhivanan

    Failing to plan is Planning to fail

Share This Page