SQL stored proc - help me write this one, please!

Question

Hi Guys,

Could you please help me with this one:

In one of my scripts I am inserting some data in to some table "Item". One of the columns in the "Item" table is "ItemNumber".

I want to be able to call some function (most probably a Stored proc) where it would return me a numeric number which I can use for the ItemNumber.

I CAN NOT use AutoIncrement because ItemNumber is unique and there might be a collision when in a differnt script I am inserting data in "Item", which already has "ItemNumber"

I only know the Max number for the "ItemNumber" and I am free to use anything after that.

I need a table for this storedProc to store the next number to be used, right?

I was thinking of creating a one column table with the MaxNumber in it and a storedproc where it would return me the MaxNumber and also increment the MaxNumber by 1 for the next use

I need help writing the stored proc and it usage with in the INSERT INTO Item call.

EDIT:

Basically I am grabbing data from 5 old tables where ItemNumber is either NULL or set. For the ones which are set they are all unique numbers. For the data where the ItemNumber is NULL I want to assign it, what the best approach?

Help please!

Thanks,

Voodoo

Answer 1

(Assuming SQL Server - probably applies to most SQL implementations)

If you use IDENTITY constraint on your field, the server will not allow there to be duplicate instances. As one of the comments notes, the value is calculated on the server in this case, and your application does not (and under normal circumstances cannot) provide it.

Note that the server does not guarantee that the numbers will be contiguous - should you do an insert, and abort the transaction, the value that would have been used would be 'lost' - the server will not offer it again.

If you need to have contiguous ID numbers, you will need to have serlialised access to update the table, which you would need to manage and which would not be efficient. (You can still declare it to be an IDENTITY column, but would need to set IDENTITY_INSERT On for the table for each update.)

Additional:

Possibly a bit of a kludge, but might work for a one-off scenario. Have you considered copying the data into a new table, where the ID is set, then applying the IDENTITY constraint, and inserting the ones with NULL IDs (allowing the server to allocate them for you?) That might fix your specific issue here.