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.