[dbo].[GetPendingXmlSystemRequests]
create procedure [dbo].[GetPendingXmlSystemRequests]
as
declare @queuedStatus int
declare @processingStatus int
select @queuedStatus = XmlSystemRequestStatusId
from XmlSystemRequestStatus
where [Name] = 'Queued'
select @processingStatus = XmlSystemRequestStatusId
from XmlSystemRequestStatus
where [Name] = 'Processing'
declare @queuedRequests table( XmlSystemRequestQueueId int )
update XmlSystemRequestQueue set
XmlSystemRequestStatusFid = @processingStatus
output deleted.XmlSystemRequestQueueId
into @queuedRequests
where XmlSystemRequestStatusFid = @queuedStatus
select
XmlSystemRequestQueue.XmlSystemRequestQueueId,
dbo.XmlSystemDataTypeMap.[Name] as XmlSystemDataTypeMap,
XmlSystemRequestStatus.[Name] as XmlSystemRequestStatus,
xmlSystemRequestType.[Name] as XmlSystemRequestType,
dbo.FormatFirstNameLastName( SysUser.FIRSTNAME, SysUser.LASTNAME ) as SysUserName,
Orders.OrderNo as OrderNumber,
Orders.PriKey as OrderId
from @queuedRequests as QueuedRequests
inner join XmlSystemRequestQueue on XmlSystemRequestQueue.XmlSystemRequestQueueId = QueuedRequests.XmlSystemRequestQueueId
inner join Sysuser on XmlSystemRequestQueue.SysUserFId = Sysuser.SysUserID
inner join dbo.Orders on XmlSystemRequestQueue.OrderFId = Orders.PriKey
inner join dbo.XmlSystemDataTypeMap on XmlSystemRequestQueue.XmlSystemDataTypeMapFId = XmlSystemDataTypeMap.XmlSystemDataTypeMapId
inner join dbo.XmlSystemRequestType on XmlSystemRequestQueue.XmlSystemRequestTypeFId = XmlSystemRequestType.XmlSystemRequestTypeId
inner join dbo.XmlSystemRequestStatus on XmlSystemRequestQueue.XmlSystemRequestStatusFId = XmlSystemRequestStatus.XmlSystemRequestStatusId
GO
GRANT EXECUTE ON [dbo].[GetPendingXmlSystemRequests] TO [MssExec]
GO