CREATE PROCEDURE csp_GetAllPatients @orderBy NVARCHAR(250) = 'Name' ,@isDescending BIT = 1 ,@pageNumber INT = 1 ,@rowsPerPage INT =10 ,@searchWord NVARCHAR(250) = null AS BEGIN SELECT COUNT(PatientId) TotalRecords FROM PatientInfo WHERE ( PatientName Like '%'+@searchWord+'%' OR CNIC Like '%'+@searchWord+'%' OR @searchWord IS NULL OR @searchWord = '' ) SELECT PatientId, [MrNumber] ,[PatientName] ,[DOB] ,[PatientAge] ,[GuardianRelation] ,[Gender] ,[ContactNumber] ,[CNIC] ,[Address] FROM PatientInfo WHERE ( PatientName LIKE '%'+@searchWord+'%' OR CNIC LIKE '%'+@searchWord+'%' OR @searchWord IS NULL OR @searchWord = '' ) ORDER BY CASE WHEN @orderBy = 'Name' AND @isDescending =1 THEN PatientName END DESC, CASE WHEN @orderBy = 'Name' AND @isDescending =0 THEN PatientName END ASC, CASE WHEN @orderBy = 'cnic' AND @isDescending =1 THEN CNIC END DESC, CASE WHEN @orderBy = 'cnic' AND @isDescending =0 THEN CNIC END ASC OFFSET ((@pageNumber - 1) * @rowsPerPage) ROWS FETCH NEXT @rowsPerPage ROWS ONLY END
0 Comments