Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SimonGough
Starting Member
23 Posts |
 Posted - 2002-01-11 : 05:51:13
|
| Hi, I have a small table containing a number for orders. Every time a user inserts, this number is grabbed using a select statement.This number is then included with the order data in the order table in an insert stored procedure.I then go back and increment the grabbed number by one for the next order...The problem i am having is that I need to some how lock this number so no-one else can grab it while an insert is going on. Would it be better for me to create 1 stored procedure with a transaction to do the whole thing? How would I lock this number until the insert and increment is complete??Thanks for your help,Simon |
|
|
smccreadie
Aged Yak Warrior
505 Posts |
Posted - 2002-01-11 : 08:17:24
|
| IF you use an IDENTITY column as your order number, the number isn't populated until the row is inserted. The next orders to come in gets the next number. There should be no need to lock anything. If there are multiple steps to inserting an order, a transaction may be helpful although I can't really tell from the information given. You shouldn't use transactions or locks if you are waiting for user input to do something. Hope that helps. If not, more information is needed. |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-01-11 : 11:00:54
|
| You should update the next order number before returning the allocated number.If you do not want there to be gaps in order numbers you will need to wait for the order to be written to the database befor allocating the number.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Locking a record