Click here to monitor SSC
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)

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: 354 | 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