ALTER procedure [mig_director].[Job_ListEventParameter] @jobId int , @limit int , @entityID uniqueidentifier , @message varchar(max) , @eventID uniqueidentifier = null as with itemCount as ( -- EITHER User Events part select top(@limit) A.[ParameterHash] , A.[ItemId] , 0 as [Type] , count(*) as [Count] from [mig_item].[EventUser] as A with (NoLock) inner join [mig_item].[ExportKey] as B with (NoLock) on A.[ItemId] = B.[ItemId] where @eventID is not null and A.[JobId] = @jobId and A.[EventID] = @eventID and B.[EntityID] = @entityID group by A.[ParameterHash] , A.[ItemId] union all -- OR System Events part select top(@limit) A.[ParameterHash] , A.[ItemId] , 1 as [Type] , count(*) as [Count] from [mig_item].[EventSystem] as A with (NoLock) inner join [mig_item].[ExportKey] as B with (NoLock) on A.[ItemId] = B.[ItemId] where @eventID is null and A.[JobId] = @jobId and A.[Message] = @message and B.[EntityID] = @entityID group by A.[ParameterHash] , A.[ItemId] ) , hashCount as ( select [ParameterHash] , [Type] , sum([Count]) as [Count] , count(*) as [Items] from itemCount group by [ParameterHash] , [Type] ) select [ParameterXml] = case A.[Type] when 0 then ( select top(1) [ParameterXml] from [mig_item].[EventUser] as B with (NoLock) where B.[ParameterHash] = A.[ParameterHash] and B.[JobId] = @jobId and B.[EventID] = @eventID ) else ( select top(1) [ParameterXml] from [mig_item].[EventSystem] as B with (NoLock) where B.[ParameterHash] = A.[ParameterHash] and B.[JobId] = @jobId and B.[Message] = @message ) end , A.[Count] , A.[Items] from hashCount as A go