存储过程返回特定间隔的记录 [英] Stored procedure to return a specific interval of records

问题描述

我必须在应用程序中创建一个包含数十万条记录的表的视图.出于显而易见的原因，我不想一次全部检索它们.

I have to make a view in an application for a table that has many hundred thousand records. For obvious reasons, I don't want to retrieve them all at once.

约定是制作存储过程来查询数据库表，所以我的计划是制作一个存储过程来返回一段记录(如记录 2000 到 3000).我知道使用嵌套查询通过 TOP 检索一系列记录的技巧，但据我所知，您无法将参数参数化为 TOP.

The convention is to make stored procedures to query the database tables, so my plan was to make a stored procedure to return an interval of records (like records 2000 to 3000). I know the trick to use a nested query to retrieve a range of records with TOP, but as far as I can tell, you can't parametrize the argument to TOP.

这将与 C# 应用程序中的数据集和 DataTable 结合使用.

This will be used in conjunction with datasets and DataTables in a C# application.

我该怎么做?

推荐答案

您可以利用 SQL Server 2008 中的 ROW_NUMBER.以下查询将根据行号仅返回 10 行.

You can take advantage of ROW_NUMBER in SQL Server 2008. Below query will returns only 10 rows based on row number.

WITH Ordered AS (
SELECT ROW_NUMBER() OVER (ORDER BY OrderID) AS RowNumber, OrderID, OrderDate
FROM Orders)
SELECT * 
FROM Ordered
WHERE RowNumber BETWEEN 21 AND 30

这篇关于存储过程返回特定间隔的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！