Help with sproc sql 2005

[Oliver1]

Hi All,

I want a to create a stored procedure which returns 5 records, at the same time it checks the same records out for 30 minutes. So far I have this, but it would suffer badly from concurrency issues, so just want some help drafting it properly.
Thanks in advance.

ALTER PROCEDURE [dbo].[Messages_GetMessages]
	@mailboxNumber varchar(50) = null,
	@state tinyint = null
AS

Update _Messages SET checkoutstamp = getdate()
Where id in (

SELECT top 5 (id)
FROM _Messages
WHERE mailbox = CASE WHEN @mailboxNumber IS NULL THEN mailbox ELSE @mailboxNumber END
AND state = CASE WHEN @state IS NULL THEN state ELSE @state END
AND (checkoutstamp < dateadd(mi, -30, getdate()) OR checkoutstamp IS NULL)
ORDER BY id DESC)


SELECT     TOP (5) ms.id, ms.mailbox, ms.timestamp, ms.state, ms.laststatechanged, ms.cli, m.duration, m.path, ms.checkoutstamp
FROM         dbo._Messages AS ms INNER JOIN
                      dbo._Message AS m ON ms.message = m.id
WHERE mailbox = CASE WHEN @mailboxNumber IS NULL THEN mailbox ELSE @mailboxNumber END
AND state = CASE WHEN @state IS NULL THEN state ELSE @state END
ORDER BY checkoutstamp DESC

[Oliver1]

Have gone for the below now. Still not a 100% happy, as still seem inelegant.

ALTER PROCEDURE [dbo].[Messages_GetMessages]
	@mailboxNumber varchar(50) = null,
	@state tinyint = null
AS
declare @tnow datetime
set @tnow = (select getdate())

DECLARE @MyTableVar table(
    myID int);
Update top (5) _Messages SET checkoutstamp = @tnow
output inserted.id into @MyTableVar
WHERE mailbox = CASE WHEN @mailboxNumber IS NULL THEN mailbox ELSE @mailboxNumber END
AND state = CASE WHEN @state IS NULL THEN state ELSE @state END
AND (checkoutstamp < dateadd(mi, -30, @tnow) OR checkoutstamp IS NULL)


SELECT      ms.id, ms.mailbox, ms.timestamp, ms.state, ms.laststatechanged, ms.cli, m.duration, m.path, ms.checkoutstamp
FROM         dbo._Messages AS ms INNER JOIN
                      dbo._Message AS m ON ms.message = m.id
WHERE ms.id in (select myid from @mytablevar)
ORDER BY checkoutstamp DESC