ALTER PROCEDURE [mst].[Utility_SortAndPage]
    @PageSize               INT,
    @CurrentPage            INT,
    @SortBy                 nvarchar(50),
    @SortAscending          bit,
    @PrimaryIDColumnName    nvarchar(100),
    @SettingsXML            xml,
    @TotalRecords           INT OUTPUT,
    @FullSortedIDList       xml OUTPUT
AS

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

DECLARE @SelectSql nvarchar(MAX), @ParamDefinition nvarchar(500);
SELECT @SelectSql = N'SELECT @TotalRecords = COUNT(*) FROM #SortTable WITH (NOLOCK)';
SET @ParamDefinition = N'@TotalRecords int OUTPUT';

EXEC sp_executesql @SelectSql, @ParamDefinition, @TotalRecords OUTPUT;

IF @PageSize IS NULL BEGIN
    SET @PageSize = @TotalRecords
END

IF @CurrentPage IS NULL BEGIN
    SET @CurrentPage = 1
END

DECLARE @START INT, @END INT
SELECT @START = ((@CurrentPage - 1) * @PageSize) + 1,
    @END = @CurrentPage * @PageSize

DECLARE @SortDirection nvarchar(5)
SELECT @SortDirection = CASE @SortAscending WHEN 1 THEN ' ASC' ELSE ' DESC' END

-- To avoid SQL injection on sort by column
-- support mulitple columns sorting
DECLARE @SortColumnCount nvarchar(50), @SortCount INT
SELECT @SortCount = COUNT(DISTINCT ColA) FROM [mst].[DelimiteredString_Separate](',',@SortBy)

SELECT @SortColumnCount = COUNT(0)
FROM tempDb.sys.COLUMNS
WHERE [object_ID] = OBJECT_ID('tempDb..#SortTable')
    AND name IN (SELECT ColA FROM [mst].[DelimiteredString_Separate](',',@SortBy))

IF @SortColumnCount = @SortCount
BEGIN
    DECLARE @Pos INT = CHARINDEX(',', @SortBy)
    DECLARE @SortSQL nvarchar(100)

    IF @Pos > 0 BEGIN
        SET @SortSQL = STUFF(@SortBy, @Pos, 0, @SortDirection)
    END ELSE BEGIN
        SET @SortSQL = @SortBy + @SortDirection
    END

    SELECT @SelectSql = '
    SELECT *
    FROM (
        SELECT ROW_NUMBER() OVER(ORDER BY ' + @SortSQL + ') AS I_ID, *
        FROM #SortTable WITH (NOLOCK)
    ) SubSort
    WHERE I_ID BETWEEN ' + CAST(@START AS nvarchar(10)) + ' AND ' + CAST(@END AS nvarchar(10)) + '
    ORDER BY I_ID
    OPTION (RECOMPILE)'

    EXEC sp_executesql @SelectSql

    IF @PrimaryIDColumnName IS NOT NULL
    BEGIN
        SELECT @SelectSql = '
        SELECT @FullSortedIDList = (
            SELECT ' + @PrimaryIDColumnName + ' AS ''@ID'',
                   ROW_NUMBER() OVER(ORDER BY ' + @SortSQL + ') AS ''@RowNumber''
            FROM #SortTable WITH (NOLOCK)
            ORDER BY ' + @SortSQL + '
            FOR XML PATH (''Item''), ROOT(''Items''), Type
        )'

        SET @ParamDefinition = N'@FullSortedIDList xml OUTPUT';

        EXEC sp_executesql @SelectSql, @ParamDefinition, @FullSortedIDList = @FullSortedIDList OUTPUT
    END
END


ALTER FUNCTION [mst].[DelimiteredString_Separate]
(   @Delim nvarchar(50),
    @Changes nvarchar(MAX)
)

RETURNS

    @TempTable TABLE
(
    ColA nvarchar(250)
)

AS
BEGIN
    DECLARE @CHANGE nvarchar(250)
    DECLARE @INDEX INT
    DECLARE @ColAValue nvarchar(250)

    SET @CHANGE = NULL

    WHILE (@Changes IS NOT NULL AND @Changes <> '')
    BEGIN
        SET @INDEX = CharIndex(@Delim, @Changes, 0)

        IF (@INDEX = 0)
        BEGIN
            SET @CHANGE = @Changes
            SET @Changes = NULL
        END
        ELSE
        BEGIN
            SET @CHANGE = LEFT(@Changes, @Index-1)
            SET @Changes = SUBSTRING(@Changes, @INDEX+1, LEN(@Changes))
        END
        INSERT INTO @TempTable
        VALUES (@CHANGE)
    END
RETURN
END
GO