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:

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
)
AS
	BEGIN

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

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.