Learn how to a build a cloud-first strategyRegister Now


Stored Procedure to increment record in a table.

Posted on 2006-05-14
Medium Priority
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
  • 3
  • 3
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16678764
>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

ID: 16678846
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16679925
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 )

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 16686470
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 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 total points
ID: 16687675
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

ID: 16705604

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.

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

810 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