Stored Procedure to increment record in a table.

Posted on 2006-05-14
Last Modified: 2008-01-09
Hello Expert.

My goal here is to use a table in a database (TOTECOUNTER) to provide and incremental counter for a inventory application.  I am creating inventory items and it is extreamly important that the barcodes I generate for each item contain a unique number.  So here is my idea.  I have a table called TOTECOUNTER.  in this table is just one record and on column (totenumber).  I would like to create a stored procedure that I can activate using a data command in my application.  The stored procedure should read the record (data type is INT) increment the value by one and replace it so that there is still just one record but its number has now increased by one.  This will be my counter to insure inventory numbers are unique.

Question by:edrosie
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    >My goal here is to use a table in a database (TOTECOUNTER) to provide and incremental counter for a inventory application.
    why not use a identity property on a integer (bigint) field (with primary key)
    this will ensure that values are generated automatically and unique (even without the primary key)

    Author Comment

    Is this an easier solution?  Can you explain further.  I am a newbee at Microsoft SQL and Visual C#.  I thought it would be easier and faster to use a stored procedure the way I explained, then from amy application I could simply use a SQLCommand (Select TOP 1 FROM TOTENUMBER ...) and I would never have to update the table from my application.
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    the problem with the SELECT TOP 1 ... to get the last value is that between the time you get the value and the time you run the insert with this value, some other user might be doing the same thing, hence 2 same id value get inserted to the database.

    now, with the identity field, you don't specify that field at all:
    CREATE TABLE test_table ( ID_FIELD INT IDENTITY, test_value varchar(40), field2 int )

    test with the query analyser:

    INSERT INTO test_table ( test_value, field2 ) VALUES ( 'this is my first line', 1 )
    INSERT INTO test_table ( test_value, field2 ) VALUES ( 'this is my second line', 2 )

    now check the contents of the table and the ID_FIELD values, you will see they are fine
    SELECT * FROM test_table

    from C#, if you need to get the id value back for any reason, you can still use a stored procedure, which has 2 input and 1 output parameter:

    CREATE PROCEDURE insert_test_table ( @test_value Varchar(40), field2 int, @ID_FIELD INT OUTPUT )
    INSERT INTO test_table ( test_value, field2 ) VALUES ( @test_value, @field2 )


    Author Comment

    Interesting and I see your point on best practice.  However here are some constraints.  I have to coexist with an old system.  Currently my inventory number must be 10 charactors.  Each reader keeps its own counter table so I don't have to worry about another user grabbing the number.

    Here is the number format.  Each reader has its own program, tables and Identifyer in the Inventory number.


         5=reader that generates the number and barcode ticket
          12345=unique inventory number this number must be 5 charactors so I need to pad it with zero's.  I was willing to omit the first 9999 possible number and start the counter @ 10000 if needed
    LVL 142

    Accepted Solution

    what about this (I hope I didn't mess the syntax):

    CREATE TABLE test_table ( ID_FIELD INT IDENTITY(10000,1), YR datetime, plant int, reader int , CODE_ID AS CONVERT(varchar(2), yr, 20) + right( '00' + cast(plant as varchar(2)),2) + cast(reader as varchar(1)) + right ( '00000' + cast(id_field as varchar(5))  ,5)  )

    insert into test_table ( yr, plant, reader ) values ( getdate(), 1, 5 )
    insert into test_table ( yr, plant, reader ) values ( getdate(), 99, 9 )

    select * from test_table


    Author Comment


    That worked great.  BTW I changed the 20 in the first convert to 2 (it gave me 20 instead of 06).

    Now all I have to do is create the sp and call it from the app.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now