Stored Procedure to increment record in a table.
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.
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.
ASKER
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.
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
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.
http://www.codeproject.com/cs/database/sqldodont.asp
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 )
AS
SET NOCOUNT ON
INSERT INTO test_table ( test_value, field2 ) VALUES ( @test_value, @field2 )
SET @ID_FIELD = SCOPE_IDENTITY()
GO
http://www.codeproject.com/cs/database/sqldodont.asp
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 )
AS
SET NOCOUNT ON
INSERT INTO test_table ( test_value, field2 ) VALUES ( @test_value, @field2 )
SET @ID_FIELD = SCOPE_IDENTITY()
GO
ASKER
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.
0601512345
06=Year
01=plant
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
Here is the number format. Each reader has its own program, tables and Identifyer in the Inventory number.
0601512345
06=Year
01=plant
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Masterful...
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.
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.
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)