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

Posted

in

,

by

Tags:


Related Posts

Comments

One response to “Pagination and Switch Case in Where clause – SQL Server”

  1. […] contained in a HAVING clause or a select list, and the column being aggregated is an outer referencePagination and Switch Case in Where clause in SQL Server var base_url_sociable = […]

Leave a Reply

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

Discover more from Jitendra Zaa

Subscribe now to keep reading and get access to the full archive.

Continue Reading