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