Re: Help with sproc sql 2005
Have gone for the below now. Still not a 100% happy, as still seem inelegant.
Code:
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