There is a ROW_NUMBER function in SQL Server2005, which will number each row returned by the SELECT statement starting from 1 and assign it a continuous number. After a sorting standard is applied during a query, the sequence can be consistent only by number. When the ROW_NUMBER function is used, A special column is also required for pre-sorting for numbering.
The code for the north wind database is as follows:
SELECT ROW_NUMBER () OVER (order by ProductID) as row, ProductName FROM Products
The running result is as follows:
ROW ProductName
-------------------------------------------------------------
1 Chai
2 Chang
3 Aniseed Syrup
4 Chef Anton's Cajun Seasoning
5 Chef Anton's Gumbo Mix
6 Grandma's Boysenberry Spread
If you want to retrieve 2-5 product names in the table, convert the above Query into a subquery, and filter the query results in the WHERE clause of the primary query. The subquery above is treated as an independent table, used in the primary query (The AS keyword after the subquery is used to specify a new name for this virtual "table) Select row, ProductName
FROM (SELECT ROW_NUMBER () OVER (order by ProductID) as row, ProductName FROM Products)
AS ProductsWithRowNumbers WHERE Row> = 2 AND Row <= 5 run the following results:
ROW ProductName
-------------------------------------------------------------
2 Chang
3 Aniseed Syrup
4 Chef Anton's Cajun Seasoning
5 Chef Anton's Gumbo Mix
Use table Variables
If you need to perform further operations on the retrieved dataset, you may need to save it in a TABEL variable. In a stored procedure, table variables are often used to store temporary data. TABLE variables can be used as normal tables.
The code for the north wind database is as follows: Create procedure MyTable
AS
DECLARE @ MyProducts table
(MyROW int,
MyProductID int,
MyProductName varchar (40 ))
BEGIN
Insert into @ MyProducts
SELECT ROW_NUMBER () OVER (order by ProductID) as row, ProductID, ProductName FROM Products
SELECT MyROW, MyProductName FROM @ MyProducts WHERE MyROW> = 2 AND MyROW <= 5
END
The result of the GO operation is as follows:
MyROW MyProductName
---------------------------------------------------
2 Chang
3 Aniseed Syrup
4 Chef Anton's Cajun Seasoning
5 Chef Anton's Gumbo Mix
Use temporary tables
If you need to perform further operations on the retrieved dataset, you may need to save it and store it in a temporary table. In a stored procedure, temporary tables are often used to store temporary data. Temporary tables can be used as normal tables.
The code for the north wind database is as follows: Create procedure [dbo]. [MyTable]
AS
Create table # MyProducts
(MyROW int,
MyProductID int,
MyProductName varchar (40 ))
BEGIN
Insert into # MyProducts
SELECT ROW_NUMBER () OVER (order by ProductID) as row, ProductID, ProductName FROM Products
SELECT MyROW, MyProductName FROM # MyProducts WHERE MyROW> = 2 AND MyROW <= 5
The result of the END operation is as follows:
MyROW MyProductName
---------------------------------------------------
2 Chang
3 Aniseed Syrup
4 Chef Anton's Cajun Seasoning
5 Chef Anton's Gumbo Mix
