Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2011

    Unanswered: Generate series from a range using procedure

    I have table which contains order number and corresponding Serial number range for those order.

    I need to create another table where I can store order number with a list of all serial numbers for that order.

    Table I have:

    Order No SerialNoStart SerialNoEnd
    1 55 66
    2 77 90
    3 233 237

    Table I need to generate from above table:

    Order No SerialNumber
    1 55
    2 56
    3 57
    | |
    | |
    3 66
    4 77
    4 78
    | |
    | |
    4 90
    5 233
    5 234
    | |
    | |
    5 237

    It would be great if anyone can show me how to get this done. I dont care if it's done using function, procedure, etc. whatever is easy.


  2. #2
    Join Date
    Apr 2011
    no one?????

  3. #3
    Join Date
    Mar 2009
    Seems to me your proposed output table doesn't correlate to the proposed input values - unless I'm misunderstanding your requirements.

    In any event - below is some code which uses a cursor to loop through your table of ranges and uses a CTE to run up the numbers within that range giving what I suspect should be the output, not what you have provided as the output.

    Bear in mind, cursors can be quite slow - so if you have a table with thousands of ranges this may not be the most optimal solution. Also, using a recursive CTE as in my example you may need to look at the MAXRECURSION query hint since the server default will be 100 and orders with a serial range greater than this will hit an error.

    -- Test Data
    Create Table Serial_Ranges (order_no int, serialstart int, serialend int)
    Create Table Serial_List (order_no int, serial_no int)
    Insert Into Serial_Ranges Values (1,55,66)
    Insert Into Serial_Ranges Values (2,77,90)
    Insert Into Serial_Ranges values (3,233,237)

    -- Cursor to loop through ranges and use CTE to create each missing number
    Declare @v_orderno int
    Declare @v_serialstart int
    Declare @v_serialend int
    Declare c_SerialCursor cursor for
    select order_no,serialstart,serialend from Serial_Ranges
    open c_SerialCursor
    fetch next from c_SerialCursor into @v_orderno, @v_serialstart, @v_serialend
    while @@fetch_status = 0
    with serialCTE As
    (select @v_serialstart as Serial
    union all
    select Serial+1
    From serialCTE
    Where Serial < @v_serialend
    Into Serial_List
    Select @v_orderno,
    From serialCTE
    fetch next from c_SerialCursor into @v_orderno, @v_serialstart, @v_serialend
    close c_SerialCursor
    deallocate c_SerialCursor

    select * from Serial_Ranges
    select * from Serial_List

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts