Pagination and Switch Case in Where clause – SQL Server

After a long time i am going to write any article on SQL Server. That last article i think was before 18 months. As currently i am exploring the Salesforce and its API’s, i caught in situation to create a stored procedure and provide the offset feature, that means i needed the pagination support in my stored procedure. There is no direct keyword available in SQL Server something like LIMIT in mysql. And therefore i thought to share my solution with community. So, here we go…

I am considering that there will be table named “Employee” in which i want to search by employees firstName with pagesize and offset attribute.

Table Structure for example:

SQL Server Employee Table
SQL Server Employee Table

Create Stored Procedure:

Below stored procedure demonstrates pagination as well as switch case in where clause.

-- Author	: Jitendra Zaa
-- Date		: 19 May 2012
-- Summary	: Search Employee on the basis of fName and lName and if the lName is blank then set
--			: default lName to search is 'Zaa'. Also there shpuld be support for pagination
CREATE PROCEDURE dbo.SearchEmployee
	@fName varchar(50), -- First Name to Search
	@lName varchar(50), -- Last Name to search, and if its blank then search for 'Zaa'
	@offset INT,		-- Starting rowNumber of record
	@pageSize INT		-- Number of records in Row

			(SELECT ROW_NUMBER() OVER(ORDER BY EmpId) AS row, --Generate Row Number
			Employee ) as e
			e.FirstName LIKE '%'+@fName+'%'
			e.LastName LIKE
				WHEN @lName <> '' THEN '%'+@lName+'%'
				ELSE 'Zaa'
				e.row BETWEEN @offSet AND (@offSet + @pageSize) --Pagination Logic

As you can see in above example, i have used “over” clause to generate the row Number. You can read more on over clause from here.

1 comment

Leave a comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.