Customized paging options using SQL server for Paging, Sorting and Searching ... Very fast and robust solution

****************************** THE SP

ALTER PROCEDURE [dbo].[caw_XYZs_GridView]
@startrec int
, @endrec int
, @statusfilterid int
, @filter_XYZname varchar(50)
, @filter_skucount varchar(50)
, @sortcolumn varchar(2)
, @sortdir varchar(4)
AS
BEGIN
/*------------------------------------------------------------------------------------------------------------------------------------------------------
Modified Date: 12-14-2015
------------------------------------------------------------------------------------------------------------------------------------------------------*/
BEGIN TRY
DECLARE @TotalRows numeric(16, 2)

IF @filter_XYZname IS NULL
AND @filter_skucount IS NULL
BEGIN
SELECT @TotalRows = count(*)
FROM vw_XYZs_List

SELECT XYZID
, XYZName
, SKUCount
FROM vw_XYZs_List
ORDER BY CASE WHEN @sortcolumn = '1' AND @sortdir = 'ASC' THEN XYZName END,
CASE WHEN @sortcolumn = '1' AND @sortdir = 'DESC' THEN XYZName END desc,
CASE WHEN @sortcolumn = '2' AND @sortdir = 'ASC' THEN SKUCount END,
CASE WHEN @sortcolumn = '2' AND @sortdir = 'DESC' THEN SKUCount END Desc
OFFSET @startRec ROWS
FETCH NEXT @endRec ROWS ONLY
END
ELSE
BEGIN
SELECT @TotalRows = count(*)
FROM vw_XYZs_List
WHERE (@filter_XYZname IS NULL OR XYZName like ('%' + @filter_XYZName + '%'))
AND (@filter_skucount IS NULL OR SKUCount LIKE ('%' + @filter_SKUCount +  '%'))

SELECT XYZID
, XYZName
, SKUCount
FROM vw_XYZs_List
WHERE (@filter_XYZname IS NULL OR XYZName like ('%' + @filter_XYZName + '%'))
AND (@filter_skucount IS NULL OR SKUCount LIKE ('%' + @filter_SKUCount +  '%'))
ORDER BY CASE WHEN @sortcolumn = '1' AND @sortdir = 'ASC' THEN XYZName END,
CASE WHEN @sortcolumn = '1' AND @sortdir = 'DESC' THEN XYZName END desc,
CASE WHEN @sortcolumn = '2' AND @sortdir = 'ASC' THEN SKUCount END,
CASE WHEN @sortcolumn = '2' AND @sortdir = 'DESC' THEN SKUCount END Desc
OFFSET @startRec ROWS
FETCH NEXT @endRec ROWS ONLY
END

SELECT TotalRows
, TotalPages
FROM dbo.fun_PagingAndRows(@TotalRows)
END TRY
BEGIN CATCH
DECLARE @ErrMsg nvarchar(4000),
@ErrSeverity int

SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()

SELECT @@TRANCOUNT
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION
END

RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
END

******************************* THE FUNCTION 

ALTER FUNCTION [dbo].[fun_PagingAndRows]

(

@totalrows int

)

RETURNS @rows TABLE

(

TotalRows int

, TotalPages int

)

AS

BEGIN

/*------------------------------------------------------------------------------------------------------------------------------------------------------

Modified Date: 10-01-2015

------------------------------------------------------------------------------------------------------------------------------------------------------*/

INSERT INTO @rows

(

TotalRows

, TotalPages

)

SELECT ISNULL(CONVERT(int, @TotalRows), 0) AS TotalRows

, CASE WHEN (CEILING(ISNULL(@TotalRows, 0) / 10.0)) < 1 THEN 1

ELSE (CEILING(ISNULL(@TotalRows, 0) / 10.0))

END AS TotalPages

RETURN

END