Stored Procedure With Searching, Sorting And Pagination In SQL Server

SELECT column_names 
FROM table_name 
ORDER BY column_names
OFFSET [n] ROWS

SELECT column_names
FROM table_name
ORDER BY column_names
OFFSET [n] ROWS   
FETCH NEXT [n1] ROWS ONLY 

-- GET_EMPLOYEE_LIST 'E','first_name','DESC',0,20
CREATE OR ALTER   PROCEDURE [dbo].[GET_EMPLOYEE_LIST]
@SEARCH_TEXT AS VARCHAR(50)='',
@SORT_COLUMN_NAME AS VARCHAR(50)='',
@SORT_COLUMN_DIRECTION AS VARCHAR(50)='',
@START_INDEX AS INT=0,
@PAGE_SIZE AS INT=10
AS
BEGIN
	DECLARE @QUERY AS VARCHAR(MAX)='',@ORDER_QUERY AS VARCHAR(MAX)='',@CONDITIONS AS VARCHAR(MAX)='',
	@PAGINATION AS VARCHAR(MAX)=''
	
	SET @QUERY='SELECT * FROM Employees '

	-- SEARCH OPERATION
	IF(ISNULL(@SEARCH_TEXT,'')<>'')
	BEGIN		
		IF(ISDATE(@SEARCH_TEXT)=1) SET @CONDITIONS=' WHERE CAST(date_of_birth AS DATE)=CAST('+@SEARCH_TEXT+'AS DATE)'
		ELSE IF(ISNUMERIC(@SEARCH_TEXT)=1)
		BEGIN
			SET @CONDITIONS=' WHERE salary='+@SEARCH_TEXT+' OR phone_number= CAST('+@SEARCH_TEXT+'AS VARCHAR(50))'
		END
		ELSE
		BEGIN
			SET @CONDITIONS='
			WHERE
			first_name LIKE ''%'+@SEARCH_TEXT+'%''
			OR first_name +'' ''+last_name LIKE ''%'+@SEARCH_TEXT+'%''
			OR last_name LIKE ''%'+@SEARCH_TEXT+'%''
			OR email LIKE ''%'+@SEARCH_TEXT+'%''
			OR gender LIKE ''%'+@SEARCH_TEXT+'%''
			OR department LIKE ''%'+@SEARCH_TEXT+'%''
			OR phone_number LIKE ''%'+@SEARCH_TEXT+'%''
		'
		END
	END
	
	-- SORT OPERATION
	IF(ISNULL(@SORT_COLUMN_NAME,'')<>'' AND ISNULL(@SORT_COLUMN_DIRECTION,'')<>'')
	BEGIN
		SET @ORDER_QUERY=' ORDER BY '+@SORT_COLUMN_NAME+' '+@SORT_COLUMN_DIRECTION
	END
	ELSE SET @ORDER_QUERY=' ORDER BY ID ASC'
	
	-- PAGINATION OPERATION
	IF(@PAGE_SIZE>0) 
	BEGIN
		SET @PAGINATION=' OFFSET '+(CAST(@START_INDEX AS varchar(10)))+' ROWS 
		FETCH NEXT '+(CAST(@PAGE_SIZE AS varchar(10)))+' ROWS ONLY'
	END
	
	IF(@CONDITIONS<>'') SET @QUERY+=@CONDITIONS
	IF(@ORDER_QUERY<>'') SET @QUERY+=@ORDER_QUERY
	IF(@PAGINATION<>'') SET @QUERY+=@PAGINATION

	PRINT(@QUERY)
	EXEC(@QUERY)
END

Explanation

In the above stored procedure, it takes five parameters which are as follows. All the parameters are assigned a default value so we can run this sp without passing any parameter.

• Search Text: In this parameter, we will pass the search value which we want to find in the record.
• Sort Column Name: In this parameter, we will pass the column name using which we are going to order the result of table data.
• Sort Column Direction: In this parameter, we will pass the value as ASC or DESC to specify in which direction we want to order the result.
• Start Index: Start index specifies the number of rows that we want to skip.
• Page Size: Page size specifies the number of records we want to show in the result.

In the body of the stored procedure, first I declare four variables to store some part of the query which we merge at the end. Following are the declared variables.

• Query: In this variable, we store the selection query and at the end merge other variables in this variable.
• Order Query: In this variable, we store the query string to sort the result.
• Conditions: In this variable, we are going to store a query of conditions for search operation.
• Pagination: In this variable, we are going to store a query that performs pagination.

Search Operation

We are going to perform a search operation if the Search Text variable is not a null or empty string.

Here we perform three types of search which are if the search value is date. The second one is if the search value is numeric and else we are going to consider it as plain text.

If the search value is date then we are going to search in only one column which is Date Of Birth. To check if the given value is a date or not we are going to use the ISDATE function of SQL which returns 1 if the value is valid date otherwise it will return 0.

If the search value is numeric then we search it in salary and phone number columns. To check given string value is a valid number we can use ISNUMBERIC function.

Lastly, if the search value is other than date and number, we consider it as plain text. We can perform a search operation by using the like operator with all columns of varchar type.

-- SEARCH OPERATION
IF(ISNULL(@SEARCH_TEXT,'')<>'')
BEGIN		
	IF(ISDATE(@SEARCH_TEXT)=1) SET @CONDITIONS=' WHERE CAST(date_of_birth AS DATE)=CAST('+@SEARCH_TEXT+'AS DATE)'
	ELSE IF(ISNUMERIC(@SEARCH_TEXT)=1)
	BEGIN
		SET @CONDITIONS=' WHERE salary='+@SEARCH_TEXT+' OR phone_number= CAST('+@SEARCH_TEXT+'AS VARCHAR(50))'
	END
	ELSE
	BEGIN
		SET @CONDITIONS='
		WHERE
		first_name LIKE ''%'+@SEARCH_TEXT+'%''
		OR first_name +'' ''+last_name LIKE ''%'+@SEARCH_TEXT+'%''
		OR last_name LIKE ''%'+@SEARCH_TEXT+'%''
		OR email LIKE ''%'+@SEARCH_TEXT+'%''
		OR gender LIKE ''%'+@SEARCH_TEXT+'%''
		OR department LIKE ''%'+@SEARCH_TEXT+'%''
		OR phone_number LIKE ''%'+@SEARCH_TEXT+'%''
	'
	END
END

Sort Operation

We will perform sort operation if sort column name and sort column direction parameter is not null or empty string.

We will assign column name and column direction with the ORDER BY clause in the Order Query variable, as you can see in the below code.

-- SORT OPERATION
IF(ISNULL(@SORT_COLUMN_NAME,'')<>'' AND ISNULL(@SORT_COLUMN_DIRECTION,'')<>'')
BEGIN
	SET @ORDER_QUERY=' ORDER BY '+@SORT_COLUMN_NAME+' '+@SORT_COLUMN_DIRECTION
END
ELSE SET @ORDER_QUERY=' ORDER BY ID ASC'

Pagination Operation

We are going to perform pagination if the page size variable has value greater than zero because here we are going to OFFSET and FETCH and FETCH only accepts values greater than zero.

We will assign the query string pagination variable as you can see in the below code.

After creating all three variables we are going to merge all those variables in a QUERY variable. And Execute this QUERY variable to get our desired output.

-- PAGINATION OPERATION
IF(@PAGE_SIZE>0) 
BEGIN
	SET @PAGINATION=' OFFSET '+(CAST(@START_INDEX AS varchar(10)))+' ROWS 
	FETCH NEXT '+(CAST(@PAGE_SIZE AS varchar(10)))+' ROWS ONLY'
END	
IF(@CONDITIONS<>'') SET @QUERY+=@CONDITIONS
IF(@ORDER_QUERY<>'') SET @QUERY+=@ORDER_QUERY
IF(@PAGINATION<>'') SET @QUERY+=@PAGINATION

When we execute this stored procedure with the given parameter it will return the result as shown in the below image.