sample store procedure for searching, sorting, count and paging

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

Post a Comment

0 Comments