Stored Procedures for document Numbers

  • CNCB    17    1
    CNPO    17    1
    CNCP    17    3
    PRT    17    5
    POD    17    7
    SCT    17    8
    EMAIL    17    16
    CSP    17    20
    BST    17    35
    ADJ    17    155

    I  have a  table with three columns document prefix,year in simple format and document last number.I want a  stored procedure to generate document numbers as follows: CNCB17000001,CNPO17000001,CNCP17000003,ADJ1700155 ETC.  For the examples above the next numbers generated should be CNCB17000002,CNPO17000002,CNCP17000004 and ADJ1700156. How best can achieve this?

    “When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris

  • "For the examples above the next numbers generated should be CNCB17000002,CNPO17000002,CNCP17000004"

    ???..."next numbers generated?"....from  where/what/which process?

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • J Livingston SQL - Tuesday, July 4, 2017 11:08 AM

    "For the examples above the next numbers generated should be CNCB17000002,CNPO17000002,CNCP17000004"

    ???..."next numbers generated?"....from  where/what/which process?

    word to the wise.....a little bit of effort on your part will pay dividends in the replies you get   

    eg:

    CREATE TABLE yourtable(
     prefix VARCHAR(5) NOT NULL
    ,Yr  INTEGER NOT NULL
    ,docno INTEGER NOT NULL
    );
    INSERT INTO yourtable(prefix,Yr,docno) VALUES
    ('CNCB',17,1),('CNPO',17,1),('CNCP',17,3),('PRT',17,5)
    ,('POD',17,7),('SCT',17,8),('EMAIL',17,16),('CSP',17,20)
    ,('BST',17,35),('ADJ',17,155);

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • J Livingston SQL - Tuesday, July 4, 2017 11:13 AM

    J Livingston SQL - Tuesday, July 4, 2017 11:08 AM

    "For the examples above the next numbers generated should be CNCB17000002,CNPO17000002,CNCP17000004"

    ???..."next numbers generated?"....from  where/what/which process?

    word to the wise.....a little bit of effort on your part will pay dividends in the replies you get   

    eg:

    CREATE TABLE yourtable(
     prefix VARCHAR(5) NOT NULL
    ,Yr  INTEGER NOT NULL
    ,docno INTEGER NOT NULL
    );
    INSERT INTO yourtable(prefix,Yr,docno) VALUES
    ('CNCB',17,1),('CNPO',17,1),('CNCP',17,3),('PRT',17,5)
    ,('POD',17,7),('SCT',17,8),('EMAIL',17,16),('CSP',17,20)
    ,('BST',17,35),('ADJ',17,155);

    public class DocNumber {
        public static String getDocCounter(String doc) {
            String co = "";
            try {
                int lastNumber = 0;
                Date date = new Date();
                SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yy");
                String docYear = simpleDateFormat.format(date);
                ArrayList<Map> results = DBConnection.sqlQuery("SELECT lastNumber FROM doccount WITH (NOLOCK) WHERE doc = '"
                        + doc + "' AND docYear = '" + docYear + "' ");
                for (Map rst : results) {
                    lastNumber = Integer.parseInt(rst.get("lastNumber").toString());
                }
                if (lastNumber == 0) {
                    lastNumber++;
                    DBConnection.sqlUpdate(
                            "INSERT INTO doccount (lastNumber, doc, docYear) VALUES (1, '" + doc + "', '" + docYear + "')");
                } else {
                    lastNumber++;
                    DBConnection.sqlUpdate("UPDATE doccount SET lastNumber = '" + lastNumber + "' WHERE doc = '" + doc
                            + "' AND docYear = '" + docYear + "' ");
                }
                co = "00000000" + lastNumber;
                co = new StringBuffer(co).reverse().toString();
                co = co.substring(0, 7);
                co = new StringBuffer(co).reverse().toString();
                co = doc + docYear + co;
            } catch (Exception e) {
                e.printStackTrace();
            }
            return co;
        }
    I  am  trying  to convert  that   code   into a   stored procedure: Year and DocPrefix are the primary keys and generation of numbers should continue.

    “When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris

  • Firstly, you will need a table to track the last DocNum used
    CREATE TABLE dbo.DocumentNumbers (
      Prefix  VARCHAR(5) NOT NULL
    , Yr   TINYINT NOT NULL
    , LastDocNo INT NOT NULL
    , PRIMARY KEY CLUSTERED (Yr, Prefix)
    );

    Next, you will  need a proc that can generate the next Document Number
    CREATE PROCEDURE dbo.GetNextDocNum
      @Prefix VARCHAR(5)
    , @Yr  TINYINT
    , @DocNum VARCHAR(20) OUTPUT
    AS
    BEGIN
    SET NOCOUNT ON;

    DECLARE @NextDocNo INT;

    INSERT INTO dbo.DocumentNumbers ( Prefix, Yr, LastDocNo )
    SELECT @Prefix, @Yr, 0
    WHERE NOT EXISTS (SELECT 1 FROM dbo.DocumentNumbers AS dn
           WHERE dn.Prefix = @Prefix
            AND dn.Yr = @Yr);

    UPDATE dbo.DocumentNumbers
    SET @NextDocNo = LastDocNo = LastDocNo +1
    WHERE Prefix = @Prefix
      AND Yr = @Yr;

    SET @DocNum = @Prefix + CONVERT(VARCHAR(10), @Yr) + RIGHT('00000000' + CONVERT(VARCHAR(10), @NextDocNo), 6)

    END;

    Finally, you will need to execute your proc to get the values.
    DECLARE @DocNum VARCHAR(20);

    EXEC dbo.GetNextDocNum
      @Prefix = 'CNCB'
    , @Yr  = 17
    , @DocNum = @DocNum OUTPUT;

    SELECT DocNum = @DocNum;

  • Can we assume that there is no possibility whatsoever that the proc will be called twice (or more) at the same time?
    If not, you need to add some code to deal with that possibility.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • If concurrency is an issue, you can also dynamically spin up SEQUENCE objects to get the next DocNumber.

    There is no need for the tracking table.  Just a proc
    CREATE PROCEDURE dbo.GetNextDocNum
      @Prefix VARCHAR(5)
    , @Yr TINYINT
    , @DocNum VARCHAR(20) OUTPUT
    AS
    BEGIN
    SET NOCOUNT ON;

    DECLARE @soName  SYSNAME;
    DECLARE @SqlCmd  NVARCHAR(1000);
    DECLARE @NextDocNo INT;

    SET @soName = '[dbo].[so' + @Prefix + CONVERT(VARCHAR(10), @Yr) + ']';
    SET @SqlCmd = N'IF OBJECT_ID(''' + @soName + N''', N''SO'') IS NULL
    BEGIN
    -- Create the new SEQ object, with the specified seed values
    CREATE SEQUENCE ' + @soName + N'
    AS INT
      MINVALUE  1
      START WITH  1
      INCREMENT BY 1
      NO CYCLE;
    END;';

    --PRINT (@SqlCmd);
    EXEC sys.sp_executesql @SqlCmd;

    SET @SqlCmd = N'SET @NextDocNo = NEXT VALUE FOR ' + @soName + N';';

    --PRINT (@SqlCmd);
    EXEC sys.sp_executesql @stmt = @SqlCmd
            , @params = N'@NextDocNo INT OUTPUT'
            , @NextDocNo = @NextDocNo OUTPUT;

    SET @DocNum = @Prefix + CONVERT(VARCHAR(10), @Yr) + RIGHT('00000000' + CONVERT(VARCHAR(10), @NextDocNo), 6)

    END;

    And call it as before
    DECLARE @DocNum VARCHAR(20);

    EXEC dbo.GetNextDocNum
    @Prefix = 'CNCB'
    , @Yr = 17
    , @DocNum = @DocNum OUTPUT;

    SELECT DocNum = @DocNum;

  • DesNorton - Tuesday, July 4, 2017 12:27 PM

    Firstly, you will need a table to track the last DocNum used
    CREATE TABLE dbo.DocumentNumbers (
      Prefix  VARCHAR(5) NOT NULL
    , Yr   TINYINT NOT NULL
    , LastDocNo INT NOT NULL
    , PRIMARY KEY CLUSTERED (Yr, Prefix)
    );

    Next, you will  need a proc that can generate the next Document Number
    CREATE PROCEDURE dbo.GetNextDocNum
      @Prefix VARCHAR(5)
    , @Yr  TINYINT
    , @DocNum VARCHAR(20) OUTPUT
    AS
    BEGIN
    SET NOCOUNT ON;

    DECLARE @NextDocNo INT;

    INSERT INTO dbo.DocumentNumbers ( Prefix, Yr, LastDocNo )
    SELECT @Prefix, @Yr, 0
    WHERE NOT EXISTS (SELECT 1 FROM dbo.DocumentNumbers AS dn
           WHERE dn.Prefix = @Prefix
            AND dn.Yr = @Yr);

    UPDATE dbo.DocumentNumbers
    SET @NextDocNo = LastDocNo = LastDocNo +1
    WHERE Prefix = @Prefix
      AND Yr = @Yr;

    SET @DocNum = @Prefix + CONVERT(VARCHAR(10), @Yr) + RIGHT('00000000' + CONVERT(VARCHAR(10), @NextDocNo), 6)

    END;

    Finally, you will need to execute your proc to get the values.
    DECLARE @DocNum VARCHAR(20);

    EXEC dbo.GetNextDocNum
      @Prefix = 'CNCB'
    , @Yr  = 17
    , @DocNum = @DocNum OUTPUT;

    SELECT DocNum = @DocNum;

    This works,thank  you so much!!!!!!!!!

    “When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris

  • Hi Des,
    The approach you used was very popular amongst front-end developers, but it was condemned quite some time ago, when I was still young and hopeful.
    It's a  factory of deadlocks.

    I prefer to stay away from UPDATE statements at any time of the day.
    For last numbers I use aggregation views.

    Here is the snippet to start from:


    USE TEMPDB

    CREATE TABLE dbo.Document (
        DocType varchar(5),
        DocYear tinyint,
        SeqNum int,
        Name nvarchar(200)
        )
    go
    create view dbo.Document_LastNumber
    as
    select DocType, DocYear, MAX(SeqNum) LastNumber
    from dbo.Document
    group by DocType, DocYear
    GO

    declare @Prefix VARCHAR(5), @Yr TINYINT, @DocumentName nvarchar(200)

    declare @N table (NewNumber int)

    select @Prefix = 'CNCB', @Yr = 17, @DocumentName = 'First one'

    insert into Document (DocType, DocYear, SeqNum, Name)
    output inserted.seqNum into @N
    select DT.DocType, DT.DocYear,
        ISNULL(LN.lastNumber, 0) + 1, DT.DocName
    FROM (
        SELECT @Prefix , @Yr, @DocumentName
        ) DT (DocType, DocYear, DocName)
        LEFT JOIN dbo.Document_LastNumber LN on LN.DocType = DT.DocType and LN.DocYear = DT.DocYear

    DELETE FROM @N
    select @Prefix = 'CNCB', @Yr = 17, @DocumentName = 'Another one of the same type'

    insert into Document (DocType, DocYear, SeqNum, Name)
    output inserted.seqNum into @N
    select DT.DocType, DT.DocYear,
        ISNULL(LN.lastNumber, 0) + 1, DT.DocName
    FROM (
        SELECT @Prefix , @Yr, @DocumentName
        ) DT (DocType, DocYear, DocName)
        LEFT JOIN dbo.Document_LastNumber LN on LN.DocType = DT.DocType and LN.DocYear = DT.DocYear

    SELECT * FROM @N
    SELECT * FROM Document

    DELETE FROM @N
    select @Prefix = 'PRT', @Yr = 17, @DocumentName = 'Some one of a new type'

    insert into Document (DocType, DocYear, SeqNum, Name)
    output inserted.seqNum into @N
    select DT.DocType, DT.DocYear,
        ISNULL(LN.lastNumber, 0) + 1, DT.DocName
    FROM (
        SELECT @Prefix , @Yr, @DocumentName
        ) DT (DocType, DocYear, DocName)
        LEFT JOIN dbo.Document_LastNumber LN on LN.DocType = DT.DocType and LN.DocYear = DT.DocYear

    SELECT * FROM @N
    SELECT * FROM Document

    _____________
    Code for TallyGenerator

  • Sergiy - Wednesday, July 5, 2017 2:50 AM

    Hi Des,
    The approach you used was very popular amongst front-end developers, but it was condemned quite some time ago, when I was still young and hopeful.
    It's a  factory of deadlocks.

    Thanks Sergiy

  • I created the procedure as per advice above the only hassle is that when transactions where the procedure is called did not commit,or fail some.It will arise in some document numbers being skipped.How do I ensure that when I call my document generating procedure in other procedures,when transactions in those did not commit the previous generated number is maintained because of failure(maintain ACID properties).Just hope was clear in my explanation.

    “When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply