Professional Web Applications Themes

impementing a complex sequence - Microsoft SQL / MS SQL Server

I have a sequence A1 to A99, B1 to B99, etc.. I'd like to do Select NextVal from MySequence and doing this would return the value in the sequence and update the sequence. Is it possible? I'd like to hear people's experience with this....

  1. #1

    Default impementing a complex sequence

    I have a sequence

    A1 to A99, B1 to B99, etc..

    I'd like to do

    Select NextVal from MySequence
    and doing this would return the value in the sequence and
    update the sequence. Is it possible?

    I'd like to hear people's experience with this.
    jason Guest

  2. #2

    Default Re: impementing a complex sequence

    Jason,

    I don't if this is the best method or not. but you can achieve this by
    creating two tables (one to store the alphabets and another 1 to store
    numbers)


    -- To get the next sequence number after 'B99'
    Select top 1 #t1.Alphabet + convert(varchar(2),#t2.Slno)
    from #t1,#t2 where #t1.Alphabet + convert(varchar(2),#t2.Slno) > 'B99'

    --DDL
    CREATE Table #T1
    ( Alphabet char(1))

    CREATE Table #T2
    ( SlNo smallInt)

    --Populate Alphabets
    Insert into #T1 values('A')
    Insert into #T1 values('B')
    Insert into #T1 values('C')
    .....
    .....
    Insert into #T1 values('Z')

    --Populate Numbers
    Insert into #T2 values(1)
    Insert into #T2 values(2)

    .....
    .....
    Insert into #T2 values(99)



    HTH

    Praveen Maddali
    MCSD, MCDBA



    "jason" <com> wrote in message
    news:0a0b01c34c58$ca084b00$gbl... 


    praveen Guest

  3. #3

    Default Re: impementing a complex sequence

    CREATE TABLE Sometable (seq CHAR(3) PRIMARY KEY CHECK (seq LIKE
    '[A-Z][0-9][0-9]'))

    INSERT INTO Sometable
    SELECT CASE WHEN RIGHT(MAX(seq),2)='99'
    THEN CHAR(ASCII(LEFT(MAX(seq),1))+1)
    ELSE COALESCE(LEFT(MAX(seq),1),'A') END+
    CASE WHEN RIGHT(MAX(seq),2)<'99'
    THEN RIGHT('00'+CAST(RIGHT(MAX(seq),2)+1 AS VARCHAR),2)
    ELSE '01' END
    FROM Sometable

    Obviously this will fail after 2574 rows.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  4. #4

    Default Re: impementing a complex sequence

    Sounds like you are trying to emulate an Oracle sequence. My suggestion is
    to use either a stored proc or an identity column. They don't work exactly
    like an Oracle sequence, but you can achieve the same result. SQL Server
    functions do not allow updates to tables.


    "jason" <com> wrote in message
    news:0a0b01c34c58$ca084b00$gbl... 


    Martin Guest

  5. #5

    Default Re: impementing a complex sequence

    Jason,

    Here is one possibility that allows you to maintain any number
    of custom sequences:

    create table SequenceMaintainer (
    SeqRef varchar(20) primary key,
    NextVal varchar(20) not null
    )
    go

    insert into SequenceMaintainer values ('ExampleOne','A99')
    insert into SequenceMaintainer values ('ExampleTwo','000001Y')
    go

    create procedure NextVal (
    SeqRef varchar(20),
    NextVal varchar(20) output
    ) as

    set NextVal = (
    select NextVal from SequenceMaintainer
    where SeqRef = SeqRef
    )

    if rowcount <> 1 begin
    set NextVal = null
    return 1 -- or raise an error here
    end

    if SeqRef = 'ExampleOne' begin
    if right(NextVal,2) = '99'
    set NextVal = char(ascii(left(NextVal,1))+1)+'1'
    else
    set NextVal = left(NextVal,1) +
    right(right(NextVal,len(NextVal)-1)+1,2)
    end

    else if SeqRef = 'ExampleTwo' begin
    if right(NextVal,1) = 'Z'
    set NextVal = right(1000000+left(NextVal,6)+1,6) + 'A'
    else
    set NextVal = left(NextVal,6) + char(ascii(right(NextVal,1))+1)
    end

    else set NextVal = null -- or raise an error

    update SequenceMaintainer set
    NextVal = NextVal
    where SeqRef = SeqRef

    return 0
    go

    declare new varchar(20)

    exec NextVal 'ExampleOne', new output
    select new
    exec NextVal 'ExampleOne', new output
    select new
    exec NextVal 'ExampleTwo', new output
    select new
    exec NextVal 'ExampleTwo', new output
    select new
    exec NextVal 'ExampleOne', new output
    select new
    exec NextVal 'ExampleOne', new output
    select new
    exec NextVal 'ExampleTwo', new output
    select new
    exec NextVal 'ExampleOne', new output
    select new


    go
    drop procedure NextVal
    drop table SequenceMaintainer


    Another possibility is to take Martin's suggestion of an identity
    column, and make your custom sequence a computed column:

    create table Jason (
    pk int identity(99,1) primary key check (pk >= 11 and pk <= 2699),
    c1 varchar(20),
    customTag as char(64+pk/99) + ltrim(pk%99+1)
    )

    insert into Jason(c1)
    select CustomerID from Northwind..Orders
    go

    select * from Jason
    go

    drop table Jason

    Steve Kass
    Drew University
    Ref: 887376B3-2E3A-4DD3-865C-D6D5546CBA90

    jason wrote:
     

    Steve Guest

Similar Threads

  1. fetch out of sequence
    By Rod in forum PHP Development
    Replies: 5
    Last Post: January 20th, 12:33 PM
  2. Gap Sequence
    By Rita in forum PERL Modules
    Replies: 6
    Last Post: November 16th, 09:28 PM
  3. sequence of function
    By trycold in forum Macromedia Flex General Discussion
    Replies: 2
    Last Post: September 7th, 08:03 PM
  4. Sequence of numbers
    By S?ren Nielsen in forum Macromedia Flash
    Replies: 1
    Last Post: October 15th, 02:10 PM
  5. yaml sequence
    By Ara.T.Howard in forum Ruby
    Replies: 0
    Last Post: October 9th, 10:59 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139