Stored procedure to "check out" data to a client app

[drewson]

I have a client app where I am checking in and out records often for about 10 clients. Every time an individual opens a record for editing, the client app sets a flag that indicates the record is checked out and assigns a username that the record is checked out to. After they close the record, it is flagged as completed.

This is the pseudocode:
1. Return the ID of the next record that is not checked out and has not been completed.
2. Update the record and set the Checked Out flag to True and the username to the logged in user.
3. Select the record with the supporting data.

Using SQL from the client, I find that two clients can "check out" the same record if they are executed at the same time because they both get the same record during step one.

If I put this logic into a stored procedure, would I be able to check out a record and return the contents of the record without having to worry about multiple clients getting the same record? I have a workaround right now, but I'm moving from Access (ugh!) to MS SQL Server and want to make sure this is done right.

Thanks!
Drew

 

[mrdenny]

Yes, using a stored procedure would be the first step. You would also want to wrap step 1 and step 2 within a BEGIN TRAN / COMMIT TRAN block to ensure that SQL locks the record after the select so that no one else can work with it until after the update is done, at which point they will be looking at the updated record and your client app won't allow them to view the record.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com