Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

GetPage - paging functionality

By vrata,

All what you need to do is to set the values of a couple of variables; NAME OF the TABLE you want to query (or you can write your own select statement here), NAMES OF COLUMNS you want to display, name of COLUMN for SORTING, define NUMBER OF ROWS on one page, NUMBER OF REQUIRED PAGE and ASCENDING/DESCENDING flag for sorting of final output.

Name of the variable Description
@table_name name of table (or view, or function, or definition of query)
@col_names, default = * name of columns to be displayed
@order_cols name of column to be used as sorting key
@pg_number number of the page, you want to see
@row_in_page number of rows on one page
@asc_flag (0,1) default=0 ascending sorting flag

examples of usage
--simple use

dbo.GetPage @table_name='sysobjects', @col_names='*',@order_cols='name',
@asc_flag='0',@row_in_page='2',@pg_number='1'

-advanced use
if you want to create your own select (eg. join over more table or select containing where, group by etc. clause), still it is possible touse GetPage stored procedure to limit the result set according to your needs.

--if you want to use SELECT with  group by condition
dbo.GetPage '(select left(name,2) a1,count(name) a2 from sysobjects group by left(name,2)) as x','*','a1','1','3','4'

Total article views: 348 | Views in the last 30 days: 1
 
Related Articles
FORUM

number the Group on data from two columns duplicated

number the Group on data from two columns duplicated

FORUM

I need to count number of columns in group

I need to count number of columns in group

FORUM

Selecting a Value of the Order Within a Group

Select To Indicate Order Number Within a Group

FORUM

Select statement with Group BY

Select statement with Group BY

FORUM

Group by clause on partial select

Group by clause on partial select

Tags
miscellaneous    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones