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 |
|
clfloyd66
Starting Member
2 Posts |
 Posted - 2004-08-16 : 13:15:21
|
| I have an issue with multiusers making calls to SQL Server at the same time with a Stored Precedure that gets the next record number (Select Max number + 1) and Inserts a record into the database and then returns the Record number to the application for further processing. If Two users try to save a record at the same time, they get the same record number returned by the time the next insert is done which again is in the same stored proc. My question is would there be a way to say Que up the calls as to a first in first out method. Is there some method of working around this issue that I just do not know about? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-08-16 : 13:17:16
|
| Use an identity on the table.orHave a separate table to hold the rec number and get it byupdate idtbl set id = id + 1, @id = @id + 1insert mytbl select @id, ....return @id==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
clfloyd66
Starting Member
2 Posts |
Posted - 2004-08-16 : 13:41:20
|
| Thanks for getting back. Its not an identity problem. Let me explain further. We have two tables. Master and Detail. We send the values of a detail record to the stored procedure. One of the perameters is the RouteNo. If we do not send the RouteNo (a new record) then it tries to get the next RouteNo. If we send the RouteNO then it just adds the detial line with the current RouteNo. There may be several lines of details added then the routeno is written out to the master record with a summary of the details.The stored proc is like this:if @ReturnTripRouteNo = 0 SELECT @RouteNo = (SELECT MAX(RouteNO + 1) FROM Actuals)if @ReturnTripRouteNo <> 0 SELECT @RouteNo = @ReturnTripRouteNo--Insert the recordINSERT INTO Actuals (RouteNo, mpp_id, Mov_Number) VALUES (@RouteNo, @mpp_id, @MoveNumber)What is happening is that when 1 user calls the proc, it gets the next RouteNo, but another user is calling the proc at the same time and before the first call inserts the line the second user has returned the same RouteNo as the first.Would your second solution remedy that? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-08-16 : 13:58:03
|
| wouldn't something like this solve your problem:INSERT INTO Actuals (RouteNo, mpp_id, Mov_Number)select case when @ReturnTripRouteNo = 0 then (select max(RouteNO+1) from Actuals) else @ReturnTripRouteNo end, @mpp_id, @MoveNumberfrom ...Go with the flow & have fun! Else fight the flow :) |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-08-17 : 18:51:47
|
| >> Would your second solution remedy that?yesAnother option is to use an identity on master.Adding a detail for a new master rec first adds the rec to master then adds the detail.This then returns the id for use in adding further detail recs.You should have a foreign key on the detail table which would prevent you trying to do this - that would have been a hint that it's going to cause problems==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
Need Some Help