--drop procedure QMgrGetQueueMessages_sel_spCREATE PROCEDURE [QMgrGetQueueMessages_sel_sp]( @ContactType char(3), @ComContactType char(3))ASSet transaction isolation level read uncommittedSELECT a.TrxOutQueueID as TrxOutQueueID, f.TrxId, convert(char, e.StartTime) as UpdateTime, a.ContactType AS ContactType, a.ComContactId AS ContactID, a.ComType AS ComType, a.ComFreqType AS ComFreqType, a.ComTime AS ComTime, coalesce(a.ComMessage, ' ') AS Message, b.ContactID AS Account, b.PhoneFax AS FaxNumber, coalesce(b.Email, ' ') AS Email, (RTrim(b.LastName) + ', ' + RTrim(b.FirstName)) AS ToName, b.PhoneBeeper as BeeperNumber, b.PagerAlpha as Alpha, b.PagerTextLen as TextLength, coalesce(a.PageNumeric, ' ') as PageNumeric, coalesce(b.PagerPIN, ' ') as PagerPIN, coalesce(c.PageEMail, ' ') as PageEMail, coalesce(b.PagerEMail, ' ') as PagerEMail, coalesce(c.PagePINDelay, 5) as PagePINDelay, coalesce(c.PageMsgDelay, 5) as PageMsgDelay, coalesce(c.PageCompanyName, ' ') as PageCompanyName, coalesce(a.PageAlpha, ' ') as PageAlpha, e.LocationID as LocationId, d.TransTypeDesc as TransTypeDesc, a.TrxDetailId as TrxDetailId, coalesce(CodesLocation.GMTToLocal, 0) as LocationHour, coalesce(c.PageCompanyCode, ' ') as PageCompanyCode, coalesce(CodesLocation.LocationFaxNumber, ' ') as LocationFaxNumber, a.ClassRegistrationId, CodesLocation.LocationFaxFormNumber, 'Y' as FaxTedFlag, coalesce(a.sendFlag,'Y') as sendFlag , f.FacilityIdinto #messagesFROM TrxQueueOut a WITH (NOLOCK) INNER JOIN Contacts b WITH (NOLOCK) ON a.ComContactId = b.ContactId AND (a.ContactType = @ContactType) INNER JOIN TrxDetail e WITH (NOLOCK) on a.TrxId = e.TrxId and a.TrxDetailId = e.TrxDetailId INNER join Trx f WITH (NOLOCK) on f.TrxId = e.TrxId and f.TrxStatus = 'CLO' INNER JOIN TransType d WITH (NOLOCK) on d.TransTypeId = e.TransTypeId LEFT JOIN PagingCompany c WITH (NOLOCK) on b.PageCompanyID = c.PageCompanyID INNER Join CodesLocation WITH (NOLOCK) on CodesLocation.LocationID = e.LocationIDwhere UpdateTime > GetDate()-4and a.QueueStatus in ('INP','QUE')and a.ComType in ('FAX','PRI','EMU','MAL','RRL')and ComContactType = @ComContactTypeand (comfreqtype in ('IMD','ANT') or (comfreqtype = 'HRY' and (datepart(mi,getdate()) between 54 and 59 or datepart(mi,getdate()) between 0 and 5)) or (comfreqtype = 'SPC' and (convert(char(5),getdate(),114) between convert(char(5), DateAdd(mi, -5, comtime), 114) and convert(char(5), DateAdd(mi, 5, comtime), 114))))update TrxQueueOutset QueueStatus = 'INP'where TrxOutQueueID in (select TrxOutQueueID from #messages)select * from #messagesorder by TrxId, ContactId, ComType, ContactTypeGO