SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE [dbo].[MYTBL](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[Designation] [nvarchar](250) NULL,
	[LastModifiedDate] [datetime] NULL,
	[LastTouchDate] [datetime] NULL,
	[CreatedDate] [datetime] NOT NULL
 CONSTRAINT [PK_OP_ProspectTBL] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO MYTBL VALUES ('a', GETUTCDATE(), GETUTCDATE(), GETUTCDATE());
INSERT INTO MYTBL VALUES ('O', GETUTCDATE(), GETUTCDATE(), GETUTCDATE());
INSERT INTO MYTBL VALUES ('Ob', GETUTCDATE(), GETUTCDATE(), GETUTCDATE());
select * from MYTBL;


ID
Designation
LastModifiedDate
LastTouchDate
CreatedDate


1
a
2020-08-13 05:11:12.353
2020-08-13 05:11:12.353
2020-08-13 05:11:12.353

2
O
2020-08-13 05:11:12.353
2020-08-13 05:11:12.353
2020-08-13 05:11:12.353

3
Ob
2020-08-13 05:11:12.353
2020-08-13 05:11:12.353
2020-08-13 05:11:12.353


DECLARE @IN_ORDERBY_COL int = 1, 
		@IN_ORDERBY_DIRECTION int  = 2, 
		@IN_PAGENEXT int = 50, 
		@IN_PAGESKIP int = 0;

SELECT	ROW_NUMBER() OVER(ORDER BY 
					(CASE WHEN @IN_ORDERBY_DIRECTION = 1 THEN
						(
							CASE 
								WHEN @IN_ORDERBY_COL = 1 THEN P.CreatedDate
								WHEN @IN_ORDERBY_COL = 2 THEN P.LastModifiedDate
								WHEN @IN_ORDERBY_COL = 3 THEN P.Designation
							END
						) 
					END) ASC,
					(CASE WHEN @IN_ORDERBY_DIRECTION = 2 THEN
						(
							CASE 
								WHEN @IN_ORDERBY_COL = 1 THEN P.CreatedDate
								WHEN @IN_ORDERBY_COL = 2 THEN P.LastModifiedDate
								WHEN @IN_ORDERBY_COL = 3 THEN P.Designation
							END
						) 
					END) DESC
				) AS [row_num],
COUNT(*) OVER () AS [total_rows],
*
FROM [dbo].[MYTBL] P WITH(NOLOCK)
ORDER BY row_num OFFSET @IN_PAGESKIP ROWS FETCH NEXT @IN_PAGENEXT ROWS ONLY;


row_num
total_rows
ID
Designation
LastModifiedDate
LastTouchDate
CreatedDate


1
3
1
a
2020-08-13 05:11:12.353
2020-08-13 05:11:12.353
2020-08-13 05:11:12.353

2
3
2
O
2020-08-13 05:11:12.353
2020-08-13 05:11:12.353
2020-08-13 05:11:12.353

3
3
3
Ob
2020-08-13 05:11:12.353
2020-08-13 05:11:12.353
2020-08-13 05:11:12.353


DECLARE @IN_ORDERBY_COL int = 3, 
		@IN_ORDERBY_DIRECTION int  = 1, 
		@IN_PAGENEXT int = 50, 
		@IN_PAGESKIP int = 0;

SELECT	ROW_NUMBER() OVER(ORDER BY 
					(CASE WHEN @IN_ORDERBY_DIRECTION = 1 THEN
						(
							CASE 
								WHEN @IN_ORDERBY_COL = 1 THEN P.CreatedDate
								WHEN @IN_ORDERBY_COL = 2 THEN P.LastModifiedDate
								WHEN @IN_ORDERBY_COL = 3 THEN P.Designation
							END
						) 
					END) ASC,
					(CASE WHEN @IN_ORDERBY_DIRECTION = 2 THEN
						(
							CASE 
								WHEN @IN_ORDERBY_COL = 1 THEN P.CreatedDate
								WHEN @IN_ORDERBY_COL = 2 THEN P.LastModifiedDate
								WHEN @IN_ORDERBY_COL = 3 THEN P.Designation
							END
						) 
					END) DESC
				) AS [row_num],
COUNT(*) OVER () AS [total_rows],
*
FROM [dbo].[MYTBL] P WITH(NOLOCK)
ORDER BY row_num OFFSET @IN_PAGESKIP ROWS FETCH NEXT @IN_PAGENEXT ROWS ONLY;
Msg 241 Level 16 State 1 Line 6
Conversion failed when converting date and/or time from character string.

ID	Designation	LastModifiedDate	LastTouchDate	CreatedDate
1	a	2020-08-13 05:11:12.353	2020-08-13 05:11:12.353	2020-08-13 05:11:12.353
2	O	2020-08-13 05:11:12.353	2020-08-13 05:11:12.353	2020-08-13 05:11:12.353
3	Ob	2020-08-13 05:11:12.353	2020-08-13 05:11:12.353	2020-08-13 05:11:12.353