Filtering, Paging and Sorting in SQL Server 2008

Copy 
1CREATE PROCEDURE [dbo].[uspTableNameOperationName] @Query      NVARCHAR(50) = NULL,
2                                                   @Offset     INT          = 0,
3                                                   @PageSize   INT          = 10,
4                                                   @Sorting    NVARCHAR(20) = 'ID DESC',
5                                                   @TotalCount INT OUTPUT
6AS
7    BEGIN
8        SET NOCOUNT ON;
9        SET @Query = LTRIM(RTRIM(@Query));
10        SELECT @TotalCount = COUNT([Id])
11        FROM [dbo].[TableName]
12        WHERE IsDeleted = 0
13              AND (@Query IS NULL
14                   OR [ColumnOne] LIKE '%' + @Query + '%'
15                   OR [ColumnTwo] LIKE '%' + @Query + '%');
16        WITH CTEResults
17             AS (SELECT Id,
18                        [ColumnOne],
19                        [ColumnTwo],
20                        [ColumnThree],
21                        ROW_NUMBER() OVER(
22                        ORDER BY CASE
23                                     WHEN(@Sorting = 'ID DESC')
24                                     THEN [Id]
25                                 END DESC,
26                                 CASE
27                                     WHEN(@Sorting = 'ID ASC')
28                                     THEN [Id]
29                                 END ASC,
30                                 CASE
31                                     WHEN(@Sorting = 'COLUMNONE ASC')
32                                     THEN [ColumnOne]
33                                 END ASC,
34                                 CASE
35                                     WHEN(@Sorting = 'COLUMNONE DESC')
36                                     THEN [ColumnOne]
37                                 END DESC,
38                                 CASE
39                                     WHEN(@Sorting = 'COLUMNTWO ASC')
40                                     THEN [ColumnTwo]
41                                 END ASC,
42                                 CASE
43                                     WHEN(@Sorting = 'COLUMNTWO DESC')
44                                     THEN [ColumnTwo]
45                                 END DESC) AS RowNum
46                 FROM [dbo].[TableName]
47                 WHERE IsDeleted = 0
48                        AND (@Query IS NULL
49                            OR [ColumnOne] LIKE '%' + @Query + '%'
50                            OR [ColumnTwo] LIKE '%' + @Query + '%');
51        SELECT *
52        FROM CTEResults
53        WHERE RowNum BETWEEN @Offset + 1 AND(@Offset + @PageSize);
54    END;
55GO