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.
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
- 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
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.
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.
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.
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.
When we execute this stored procedure with the given parameter it will return the result as shown in the below image.
0 Comments