Stored Procedure With Searching, Sorting And Pagination In SQL Server

In this article, we will create a Stored Procedure in SQL Server that fetches data from tables and performs operations like searching, sorting, and pagination based on parameters passed in that stored procedure and return data. To know about what is Stored Procedure, how to create a Stored Procedure and other details click here.

First, we must clarify some concepts before starting our stored procedures like OFFSET and FETCH. We are going to use OFFSET and FETCH to achieve pagination in our stored procedure

OFFSET

Offset in SQL is used to eliminate or skip a set of records from a given table in order to retrieve a set of records according to the requirement of the database.

Basically, it is used to find a starting point to display a set of rows as a final output. By using OFFSET, we discard the desired number of rows from the beginning of the table till the number mentioned with the OFFSET command.

You have to use ORDER BY if you want to use OFFSET.

Syntax

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

• In the above syntax [n] is a number that we want to skip from the start. If we pass 10 in place of [n] then it will skip the first 10 rows from the result and return other rows.
• Here [n] must be an integer value that is greater than or equal to 0.

FETCH 

FETCH is an SQL command used along with the ORDER BY clause with an OFFSET(Starting point) to retrieve or fetch selected rows sequentially using a cursor that moves and processes each row one at a time till the number of rows mentioned in the query is displayed.

• With the OFFSET and FETCH clause, the ORDER BY is mandatory to be used.
• The number of rows specified after FETCH must be greater than 0.
• With FETCH the OFFSET clause is mandatory. You are not allowed to use, FETCH direct after ORDER BY
• The OFFSET/FETCH row count expression can only be any arithmetic, constant, or parameter expression which will return an integer value.
• You are not allowed to combine TOP with OFFSET and FETCH.

Syntax

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

Here n is the value of rows that we want to skip in output and [n1] is the number of records that we want to show. If we pass 10 in [n] and 5 in [n1] then it will skip the first 10 records from the result and return a total of 5 rows in the output.

Here, we are going to create a stored procedure to perform operations on the Employees table. In the image below you can see that in our table there are 1000 rows.

Check out the following code of our stored procedure.

-- 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.