All geek questions in one place

Stored procedure to return a specific record interval

I need to make a view in an application for a table containing many hundreds of thousands of records. For obvious reasons, I do not want to immediately extract them.

The convention is to make stored procedures for querying database tables, so my plan was to save the stored procedure to return the interval of records (e.g. records from 2000 to 3000). I know a trick to use a nested query to get a range of records using TOP , but as far as I can tell, you cannot parameterize the TOP argument.

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

How can I do it?

+4
source share
2 answers

You can use ROW_NUMBER in SQL Server 2008. Only 10 rows will be returned below the query based on the 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
+4
source

You can parameterize the argument to the top. Insert the argument into (curly braces).

If you still need everything, it’s more efficient to just get all of them in one go. No need to bite.

+3
source

Source: https://habr.com/ru/post/1412656/

More articles:


All Articles