SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Return a Subset of Data from a Table

By davidbitton,

The ability to page in SQL Server is one of those things that everyone wants, but can't quite seem to get from Microsoft. Many ideas have been posted, each claiming to be _the_ way to do it. In the spirit of mine is better than yours, I've implemented my own paging scheme.

A feature that is in the upcoming version of SQL Server is the ability to supply a variable to the TOP keyword. Well, as it turns out, this functionality does exist in SQL Server 2000. The only difference is the syntax. To limit the number of rows returned from a query based on a variable is to use SET ROWCOUNT @Foo. Don't forget, however, to reverse that after the query by setting ROWCOUNT to 0.

The principle of which this works is that I return all of the primary keys in a table into a table datatype up to and including the record that i want to start at. Then I grab that value by selecting the TOP 1 from that temp table, ordering by the value, descending. Now I have the starting primary key value.

At this point, it's a matter of looping through the records, row by row until I have filled my cache table with a single page's worth of data. Once that is complete, I return the contents of the cache table. In addition, in the form of an OUTPUT param, I return the total number of rows in the table. I have this value as a result of a sanity check that stops the proc if we are requesting a page of data that is past the end of the table.

I hope this sproc finds you well.

Total article views: 534 | Views in the last 30 days: 2
Related Articles

Return data from a vb.net DataTable object to SQL Server

Process to return modified data to SQL Server from a datatable object in a CLR Assembly


Installation of SQL Server 2005 on cluster returns error

Installation of SQL Server 2005 on cluster returns error


SQLCMD not returning control

running scripts against remote server via sqlcmd control not returned to calling script


Getting "OLE DB provider "STREAM" for linked server "(null)" returned message "Query timeout expired"."

Getting "OLE DB provider "STREAM" for linked server "(null)" returned message "Query timeout expired...


How to get the return value from Exec Executed as command at the Linked Server

To get the return value of a storedProcedure at linked server.

data warehousing