StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POBest practise custom paging SQL server 2005
text
Body
copied!<p>I'm got trouble with paging on SQL server 2005 which run query 2 times (get data and total rows). My demo proc below which base on Northwind database. Please help me to how to get total rows without query again.</p> <pre><code>CREATE PROCEDURE [dbo].[SearchEmployees] @search nvarchar(4000) ,@orderBy varchar(200) ,@PageSize int ,@PageIndex int ,@TotalRowsNumber int output AS DECLARE @PageLowerBound int DECLARE @PageUpperBound int SET @PageLowerBound = @PageSize * @PageIndex SET @PageUpperBound = @PageSize + @PageLowerBound --Default order by to first column IF (@OrderBy is null or LEN(@OrderBy) < 1) BEGIN SET @OrderBy = 'EmployeeID DESC' END -- SQL Server 2005 Paging declare @SQL as nvarchar(4000) declare @SQLCOUNT as nvarchar(4000) declare @Param as nvarchar(500) SET @SQL = 'WITH PageIndex AS (' SET @SQL = @SQL + ' SELECT ' IF @PageSize > 0 BEGIN SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound) END SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex ' SET @SQL = @SQL + ' ,EmployeeID' SET @SQL = @SQL + ', LastName' SET @SQL = @SQL + ', FirstName' SET @SQL = @SQL + ', Title' SET @SQL = @SQL + ' FROM employees' IF Len(@Search) > 0 BEGIN SET @SQL = @SQL + ' ' + @Search END SET @SQL = @SQL + ') SELECT ' SET @SQL = @SQL + ' EmployeeID' SET @SQL = @SQL + ', LastName' SET @SQL = @SQL + ', FirstName' SET @SQL = @SQL + ', Title' SET @SQL = @SQL + ' FROM PageIndex ' SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound) IF @PageSize > 0 BEGIN SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound) END --Get Row Count SET @SQLCOUNT = 'SELECT @TotalRowsNumber = count(EmployeeID) FROM employees' SET @Param = N'@Search nvarchar(2000),@TotalRowsNumber INT OUTPUT' IF LEN(@Search) > 0 BEGIN SET @SQLCOUNT = @SQLCOUNT + @Search END exec sp_executesql @SQL exec sp_executesql @SQLCOUNT, @Param,@Search=@Search,@TotalRowsNumber = @TotalRowsNumber OUT </code></pre> <p>Thanks in advance!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload