• Chris Harshman - Friday, February 23, 2018 11:01 AM

    nickosl50 - Friday, February 23, 2018 10:30 AM

    I have a table  ( invoiceTypes)  that stores various invoice types and for each type its next available number (integer)
    When I enter a new invoice ( in a table invoices) and press save I start a transaction in which I want to get the next available number from invoiceTypes table  for the type of invoice I entered. I want to be sure that no other user gets the same number for this invoice type (since (invoice type and number) is a unique key for invoices table). After I  commit my transaction other users can access the invoiceTypes table.

    I would be grateful if you could propose me a solution to the above problem.

    Thank you

    As Steve already mentioned, this sounds like the exact situation that a SEQUENCE would be used for.  You can create a different SEQUENCE for each type of Invoice that you need to track:
    https://docs.microsoft.com/en-us/sql/relational-databases/sequence-numbers/sequence-numbers

    Although, if the numbers are already stored in a table, an update that assigns value to a variable would work if it's inside a transaction. This shouldn't create problems unless you start using NOLOCK hints or READ_UNCOMMITED isolation level everywhere.


    UPDATE t SET
        @InvoiceNumber = InvoiceNumber = InvoiceNumber + 1
    FROM InvoiceTypes
    WHERE InvoiceType = @InvoiceType;

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2