Stored Procedure to Select/Update multiple records

Question

question

jose asked Nov 3, '09

Stored Procedure to Select/Update multiple records

I have a stored procedure that retrieves N records from a table, those records are presented to the user1 and can not be presented to another user until the user1 process them.

So I need my stored procedure retrieve the records at the same time block for further reading.

For ONE record I found a solution which I place below, but I'm getting to modify multiple records ... any help please?

Solution for ONE record: http://stackoverflow.com/questions/562467/sql-server-select-update-stored-procedure-weirdness

CREATE PROCEDURE get_from_q
AS
DECLARE @queueid INT
BEGIN TRANSACTION TRAN1
SELECT TOP 1 @queueid = id FROM MSG_Q WITH (updlock, readpast) WHERE MSG_Q.status=0
SELECT TOP 1 * FROM MSG_Q WHERE MSG_Q.id=@queueid
UPDATE MSG_Q SET status=1 WHERE id=@queueid
COMMIT TRANSACTION TRAN1

stored-procedures update blocking

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2009-11-03T11:28:01Z

Matt Whitfield answered Nov 3, '09

You could just select the records you want into a table variable:

DECLARE @targetRowIDs TABLE (rowID int NOT NULL)
INSERT INTO @targetRowIDs (rowID)
SELECT ID FROM MSG_Q WITH (updlock, readpast) WHERE MSG_Q.status=0
SELECT TOP 1 * FROM MSG_Q WHERE MSG_Q.id IN (SELECT rowID FROM @targetRowIDs)
UPDATE MSG_Q SET status=1 WHERE id IN (SELECT rowID FROM @targetRowIDs)

Note that you'll have to modify the top select to select the rows you want.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2009-11-03T12:03:43Z

Jesse McLain answered Nov 3, '09

Do you have an application on the front-end that controls user access to the underlying data? If so, you might be better implementing a solution that makes use of a 'selected rows' table that holds the record ids of the rows selected, along with the user name and any pertinent datetime info. This solution has the advantage of being able to "lock" rows beyond the SQL session in which they were selected (i.e., you wouldn't need a transaction to lock them).

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2009-11-04T15:29:45Z

Blackhawk-17 answered Nov 4, '09

If you are creating a process whereby the in use records are limited to only the given user (no SELECTs against them either) you could kludge a solution by moving them into a temporary table so they were completely gone, then put them back upon completion of user processing.

Pretty good chance of data loss if not done correctly but it would be an option.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 4 · 2009-11-12T16:21:50Z

Jeff Oresik answered Nov 12, '09

Some apps use an IsLocked bit field. When the records need to be held for a data change, the lock is set. The next user can select records where IsLocked = 1, but not in a data change mode. Saving the recordset back to the database needs a procedure to 'unlock'.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question