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'
Creating a PDF from a Stored Procedure in SQL Server
A short but interesting article, the author has figured out a way to create a PDF from a stored procedure without using a third party library.
2019-09-20 (first published: 2003-08-26)
72,798 reads