+---------------+---------------+----------+
|  ColumnName   |   DataType    | Nullable |
+---------------+---------------+----------+
| Id            | INT           | NOT NULL |
| CreatedDate   | DATETIME      | NOT NULL |
| ApiMethodName | NVARCHAR(MAX) | NOT NULL |
| Request       | NVARCHAR(MAX) | NOT NULL |
| Result        | NVARCHAR(MAX) | NOT NULL |
| Method_Id     | INT           | NOT NULL |
| User_Id       | INT           | NULL     |
+---------------+---------------+----------+

+--------------------+----------+----------+
|     ColumnName     | DataType | Nullable |
+--------------------+----------+----------+
| Id                 | INT      | NOT NULL |
| AuditUserMethod_Id | INT      | NOT NULL |
| ErrorCode          | INT      | NOT NULL |
+--------------------+----------+----------+

CREATE PROCEDURE [api].[Audit_V1_GetAuditDetails]
(
    @Users XML = NULL,
    @Methods XML = NULL,
    @ErrorCodes XML = NULL,
    @FromDate DATETIME = NULL,
    @ToDate DATETIME = NULL,
    @PageSize INT = 5,
    @PageIndex INT = 0
)
AS
BEGIN

DECLARE @UserIds            TABLE   (Id INT)
DECLARE @MethodNames            TABLE   (Name NVARCHAR(256))
DECLARE @ErrorCodeIds       TABLE   (Id INT)

DECLARE @FilterUsers        BIT = 0
DECLARE @FilterMethods      BIT = 0
DECLARE @FilterErrorCodes   BIT = 0

INSERT @UserIds
    SELECT
        x.y.value('.', 'int')
    FROM @Users.nodes('Ids/x/@i') AS x (y)
INSERT @MethodNames
    SELECT
        x.y.value('.', 'NVARCHAR(256)')
    FROM @Methods.nodes('ArrayOfString/string') AS x (y)
INSERT @ErrorCodeIds
    SELECT
        x.y.value('.', 'int')
    FROM @ErrorCodes.nodes('Ids/x/@i') AS x (y)

IF EXISTS (SELECT TOP 1 0 FROM @UserIds)
SET @FilterUsers = 1
IF EXISTS (SELECT TOP 1 0 FROM @MethodNames)
SET @FilterMethods = 1
IF EXISTS (SELECT TOP 1 0 FROM @ErrorCodeIds)
SET @FilterErrorCodes = 1

DECLARE @StartRow INT = @PageIndex * @Pagesize

DECLARE @PageDataResults TABLE
(
    Id INT,
    CreatedDate DATETIME,
    ApiMethodName NVARCHAR(256),
    Request NVARCHAR(MAX),
    Result NVARCHAR(MAX),
    MethodId INT,
    UserId INT,
    TotalRows INT
);

WITH PageData AS
(
    SELECT
        id                                              AS id
        ,createddate                                    AS createddate
        ,apimethodname                                  AS apimethodname
        ,request                                        AS request
        ,result                                         AS result
        ,method_id                                      AS method_id
        ,user_id                                        AS user_id
        ,ROW_NUMBER() OVER (ORDER BY createddate DESC, id DESC) AS row_number
        ,COUNT(*) OVER() as TotalRows
    FROM dbo.AuditUserMethods AS aum
    WHERE (@FromDate IS NULL OR (@FromDate IS NOT NULL AND aum.createddate > @FromDate))
    AND (@ToDate IS NULL OR (@ToDate IS NOT NULL AND aum.createddate < @ToDate))
    AND (@FilterUsers = 0 OR (@FilterUsers = 1 AND aum.user_id IN (SELECT Id FROM @UserIds)))
    AND (@FilterMethods = 0 OR (@FilterMethods = 1 AND aum.ApiMethodName IN (SELECT Name FROM @MethodNames)))
    AND
        (
            @FiltererRorCodes = 0 OR
                (
                    @FiltererRorCodes = 1 AND EXISTS
                        (
                            SELECT 1
                            FROM AuditUserMethodErrorCodes e
                            WHERE e.AuditUserMethod_Id = aum.Id
                            AND e.ErrorCode IN (SELECT Id FROM @ErrorCodeIds)
                        )
                )
        )
)
INSERT @PageDataResults
SELECT TOP (@Pagesize)
     PageData.id                AS id
    ,PageData.createddate   AS createddate
    ,PageData.apimethodname AS apimethodname
    ,PageData.request       AS request
    ,PageData.result        AS result
    ,PageData.method_id     AS method_id
    ,PageData.user_id       AS user_id
    ,PageData.TotalRows     AS totalrows
    FROM PageData
WHERE PageData.row_number > @StartRow
ORDER BY PageData.createddate DESC

SELECT Id, CreatedDate, ApiMethodName, Request, Result, MethodId, UserId
FROM @PageDataResults

SELECT aumec.AuditUserMethod_Id, aumec.ErrorCode
FROM @PageDataResults ps
INNER JOIN AuditUserMethodErrorCodes aumec ON ps.Id = aumec.AuditUserMethod_Id

SELECT TOP 1 TotalRows NumberOfReturnedAuditEntries FROM @PageDataResults

END

EXEC [api].[Audit_V1_GetAuditDetails]   @Users = N'<Ids><x i="1" /></Ids>'
                                        ,@Methods = NULL
                                        ,@ErrorCodes = N'<Ids />'
                                        ,@FromDate = '2015-02-15 07:18:59.613'
                                        ,@ToDate = '2015-07-02 08:18:59.613'
                                        ,@Pagesize = 5000
                                        ,@PageIndex = 0