在示例的帮助下如何在sql server 2005中进行sql server分页 [英] how to do sql server paging in sql server 2005 with help of example

问题描述

如何在sql server 2005中使用示例

how to do sql server paging in sql server 2005 with help of example

推荐答案

来执行sql server分页



请参阅以下链接



1. SQL Server 2005分页结果 [ ^ ]



2. SQL SERVER - 2005 T-SQL分页查询技术比较 [< a href =http://blog.sqlauthority.com/2007/06/11/sql-server-2005-t-sql-paging-query-technique-comparison-over-and-row_number-cte-vs-derived- table /target =_ blanktitle =New Window> ^ ]



3. Paging在SQL Server 2005中 [ ^ ]



4. SQL Server 2005分页示例 [ ^ ]



检查这个 [ ^ ]文章。这将帮助您解决问题





创建如下的Sp

Hi,

Refer below link

1. SQL Server 2005 Paging Results[^]

2. SQL SERVER – 2005 T-SQL Paging Query Technique Comparison [^]

3. Paging in SQL Server 2005 [^]

4. SQL Server 2005 Paging Sample [^]

Check this[^] article. This will help you for solving your problem


Create Sp like below

CREATE PROCEDURE [dbo].[Paging]
(
  @Page int, -- pass page number
  @PageSize int -- pass number of records for page
)
AS
SET NOCOUNT ON;

SELECT TOP(@PageSize) * FROM
(
 SELECT RowID = ROW_NUMBER() OVER (ORDER BY Orderbycolumn ), col1,col2,col3 , TotalRows=Count(*) OVER()
 FROM tbsalesheader
)
A WHERE A.RowID > ((@Page-1)*@PageSize)
SET NOCOUNT OFF

CREATE PROC GetCustomersByPage

@PageSize int, @PageNumber int 

AS 

Declare @RowStart int 
Declare @RowEnd int 

if @PageNumber > 0 
Begin 

SET @PageNumber = @PageNumber -1 

SET @RowStart = @PageSize * @PageNumber + 1; 
SET @RowEnd = @RowStart + @PageSize - 1 ; 

With Cust AS 
     ( SELECT CustomerID, CompanyName, 
       ROW_NUMBER() OVER (order by CompanyName) as RowNumber 
       FROM Customers ) 

select * 
from Cust 
Where RowNumber >= @RowStart and RowNumber <= @RowEnd end

END

/ * SQL SERVER - 2005 T-SQL分页查询技术比较（OVER和ROW_NUMBER（）） - CTE与派生表：* /

/*SQL SERVER – 2005 T-SQL Paging Query Technique Comparison (OVER and ROW_NUMBER()) – CTE vs. Derived Table:*/

--SQL 2005 Paging Method Using Derived Table:
USE AdventureWorks
GO
DECLARE @StartRow INT
DECLARE @EndRow INT
SET @StartRow = 120
SET @EndRow = 140
SELECT FirstName, LastName, EmailAddress
FROM (
SELECT PC.FirstName, PC.LastName, PC.EmailAddress,
ROW_NUMBER() OVER(
ORDER BY PC.FirstName, PC.LastName,PC.ContactID) AS RowNumber
FROM Person.Contact PC) PersonContact
WHERE RowNumber > @StartRow
AND RowNumber < @EndRow
ORDER BY FirstName, LastName, EmailAddress
GO
--SQL 2005 Paging Method Using CTE:
USE AdventureWorks
GO
DECLARE @StartRow INT
DECLARE @EndRow INT
SET @StartRow = 120;
SET @EndRow = 140;
WITH PersonContact AS
(
SELECT PC.FirstName, PC.LastName, PC.EmailAddress,
ROW_NUMBER() OVER(
ORDER BY PC.FirstName, PC.LastName,PC.ContactID) AS RowNumber
FROM Person.Contact PC)
SELECT FirstName, LastName, EmailAddress
FROM PersonContact
WHERE RowNumber > @StartRow
AND RowNumber < @EndRow
ORDER BY FirstName, LastName, EmailAddress
GO

这篇关于在示例的帮助下如何在sql server 2005中进行sql server分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！