CREATE PROCEDURE [dbo].[GetPatientWithPaging]
@Page INT,
@Size INT,
@SortColumn nvarchar(50)
AS
BEGIN
SELECT * FROM Patients
ORDER BY
CASE
@SortColumn WHEN 'Id' THEN Id
WHEN 'Name' THEN Name
WHEN 'DOB' THEN CONVERT(nvarchar(10), DayOfBirdth)
WHEN 'Email' THEN Email
WHEN 'PhoneNumber' THEN PhoneNumber
END
OFFSET (@Page - 1) * @Size ROWS
FETCH NEXT @Size ROWS ONLY
END