Tens of millions of common paging stored procedures

/*

After testing, 14483461 pages are queried in 100,000th records. The first and second time of 10 records on each page is 0.47 seconds in ascending and descending order, and the second time is 0.43 seconds. The test syntax is as follows:

Exec getrecordfrompage news, newsid, 10, 100000

News is the table name and newsid is the key field. Create an index for newsid before use.

*/

/*

Function Name: getrecordfrompage

Function: obtains data on a specified page.

Parameter description: @ tblname indicates the name of the table containing data.

@ Fldname key field name

@ Pagesize number of records per page

@ Pageindex: page number to be obtained

@ Ordertype: Sorting type. Values: 0 (ascending) and 1 (descending ).

@ Strwhere query condition (Note: Do not add where)

*/

Create procedure getrecordfrompage

@ Tblname varchar (255), -- table name

@ Fldname varchar (255), -- field name

@ Pagesize Int = 10, -- page size

@ Pageindex Int = 1, -- page number

@ Ordertype bit = 0, -- set the sorting type. If the value is not 0, the sorting type is descending.

@ Strwhere varchar (2000) = ''-- Query condition (Note: Do not add where)

As

Declare @ strsql varchar (6000) -- subject sentence

Declare @ strtmp varchar (1000) -- Temporary Variable

Declare @ strorder varchar (500) -- sort type

If @ ordertype! = 0

begin

set @ strtmp = '<(select min'

set @ strorder = 'order by ['+ @ fldname +'] desc'

end

else

begin

set @ strtmp = '> (select Max'

set @ strorder = 'order by ['+ @ fldname +'] ASC '

end

set @ strsql = 'select top' + STR (@ pagesize) + '* from ['

+ @ tblname + '] Where [' + @ fldname + ']' + @ strtmp + '(['

+ @ fldname + ']) from (select top' + STR (@ PageIndex-1) * @ pagesize) + '['

+ @ fldname + '] from [' + @ tblname + ']' + @ strorder + ') as tbltmp) '

+ @ strorder

If @ strwhere! = ''

set @ strsql = 'select top' + STR (@ pagesize) + '* from ['

+ @ tblname + '] Where [' + @ fldname + ']' + @ strtmp + '(['

+ @ fldname + ']) from (select top' + STR (@ PageIndex-1) * @ pagesize) + '['

+ @ fldname + '] from [' + @ tblname + '] Where' + @ strwhere +''

+ @ strorder + ') as tbltmp) and' + @ strwhere +'' + @ strorder

If @ pageindex = 1

Begin

Set @ strtmp =''

If @ strwhere! =''

Set @ strtmp = 'where ('+ @ strwhere + ')'

Set @ strsql = 'select top '+ STR (@ pagesize) +' * from ['

+ @ Tblname + ']' + @ strtmp + ''+ @ strorder

End

Exec (@ strsql)

Go

Method 2:

/* -- Pagination implemented by stored proceduresProgram

Displays page X of the specified table, view, and query result.

In the case of primary keys or ID columns in the table, query the data directly from the original table. In other cases, use the temporary table method.

This method is not recommended if the view or query result contains a primary key.

*/

/* -- Call example

Exec p_show 'region information'

Exec p_show 'region information', 5, 3, 'region No., Region name, mnemonic code ', 'region No'

--*/

/*

The query statements with sorting must be sorted first and then output the result. That is:

Exec p_show 'select Top 100 percent * From region data order by region name', 'region number, Region name, mnemonic code ', 'region name'

-- Query statement plus: Top 100 percent // top

*/

If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [p_show] ') and objectproperty (ID, n' isprocedure') = 1)

drop procedure [DBO]. [p_show]

go

Create proc p_show

@ querystr nvarchar (4000 ), -- table name, view name, and query statement

@ pagesize Int = 10, -- size of each page (number of rows)

@ pagecurrent Int = 1, -- page to be displayed

@ fdshow nvarchar (4000) ='', -- List of fields to be displayed. If the query result contains an ID field, you must specify this value, the field

@ fdorder nvarchar (1000) = ''-- List of sorting fields

as

declare @ fdname nvarchar (250) -- primary key in the table or the column name in the table or temporary table

, @ id1 varchar (20), @ Id2 varchar (20) -- number of the start and end records

, @ obj_id int -- Object ID

-- processing of compound primary keys in the table

declare @ strfd nvarchar (2000) -- composite primary key list

, @ strjoin nvarchar (4000) -- connection field

, @ strwhere nvarchar (2000) -- Query condition

select @ obj_id = object_id (@ querystr)

, @ fdshow = case isnull (@ fdshow ,'') when ''then'' * 'else' '+ @ fdshow end

, @ fdorder = case isnull (@ fdorder ,'') when ''then'' else' order by '+ @ fdorder end

, @ querystr = case when @ obj_id is not null then ' ' + @ querystr else '(' + @ querystr + ') a' end

-- If the first page is displayed, top can be used directly.

If @ pagecurrent = 1

Begin

Select @ id1 = cast (@ pagesize as varchar (20 ))

Exec ('select top '+ @ Id1 + @ fdshow +' From '+ @ Querystr + @ fdorder)

Return

End

-- If it is a table, check whether there is an identifier or a primary key in the table.

If @ obj_id is not null and objectproperty (@ obj_id, 'istable') = 1

Begin

Select @ id1 = cast (@ pagesize as varchar (20 ))

, @ Id2 = cast (@ PageCurrent-1) * @ pagesize as varchar (20 ))

Select @ fdname = Name from syscolumns where Id = @ obj_id And status = 0x80

If @ rowcount = 0 -- if the table has no ID column, check whether the table has a primary key.

Begin

If not exists (select 1 from sysobjects where Parent_obj = @ obj_id And xtype = 'pk ')

Goto lbusetemp -- if the table has no primary key, use a temporary table for processing.

Select @ fdname = Name from syscolumns where Id = @ obj_id And colid in (

Select colid from sysindexkeys where @ obj_id = ID and indid in (

Select indid from sysindexes where @ obj_id = ID and name in (

Select name from sysobjects where xtype = 'pk' and Parent_obj = @ obj_id

)))

If @ rowcount> 1 -- check whether the primary key in the table is a composite primary key

Begin

Select @ strfd = '', @ strjoin ='', @ strwhere =''

Select @ strfd = @ strfd + ', [' + name + ']'

, @ Strjoin = @ strjoin + 'and A. [' + name + '] = B. [' + name + ']'

, @ Strwhere = @ strwhere + 'and B. [' + name + '] Is null'

From syscolumns where Id = @ obj_id And colid in (

Select colid from sysindexkeys where @ obj_id = ID and indid in (

Select indid from sysindexes where @ obj_id = ID and name in (

Select name from sysobjects where xtype = 'pk' and Parent_obj = @ obj_id

)))

Select @ strfd = substring (@ strfd, 2,2000)

, @ Strjoin = substring (@ strjoin, 5)

, @ Strwhere = substring (@ strwhere, 5)

Goto lbusepk

End

End

End

Else

Goto lbusetemp

/* -- Use the identification column or primary key as a single field --*/

Lbuseidentity:

Exec ('select top '+ @ Id1 + @ fdshow +' From '+ @ Querystr

+ 'Where '+ @ Fdname +' Not in (select top'
+ @ Id2 +' '+ @ Fdname +' From '+ @ Querystr + @ fdorder

+ ')' + @ Fdorder

)

Return

/* -- The table has a composite primary key --*/

Lbusepk:

Exec ('select '+ @ Fdshow +' From (select top '+ @ Id1 +' A. * from

(Select Top 100 percent * from '+ @ Querystr + @ fdorder +')

Left join (select top '+ @ Id2 +' '+ @ Strfd +'

From '+ @ Querystr + @ fdorder +') B on '+ @ Strjoin +'

Where '+ @ Strwhere +')'

)

Return

/* -- use a temporary table -- */

lbusetemp:

select @ fdname = '[ID _' + Cast (newid () as varchar (40) + ']'

, @ id1 = cast (@ pagesize * (@ PageCurrent-1) as varchar (20)

, @ Id2 = cast (@ pagesize * @ PageCurrent-1 as varchar (20)

Exec ('select '+ @ Fdname +' = identity (INT, 0, 1), '+ @ fdshow +'

Into # TB From '+ @ querystr + @ fdorder +'

Select '+ @ Fdshow +' From # TB where '+ @ Fdname +' Between'
+ @ Id1 +' And '+ @ Id2

)

Go

ProgramCodeCall

Sqlconnection con = com. sqlcon;

Con. open ();

Sqlcommand comm = new sqlcommand ();

Comm. commandtype = commandtype. storedprocedure;

Comm. commandtext = "p_show ";

Comm. Connection = con;

Comm. parameters. add ("@ querystr", system. data. sqldbtype. (varchar, 4000 ). value = "select * from Web where repository number like '%" + this. ddltype. selectedvalue. trim () + "% 'and name like' %" define this.txt name. text. trim () + "% 'and quantity>" +convert.toint32(this.txt qty. text. trim ());

Comm. Parameters. Add ("@ fdshow", system. Data. sqldbtype. varchar, 4000). value = "No., name, quantity ";

Comm. Parameters. Add ("@ pagesize", system. Data. sqldbtype. INT). value = This. pagesize;

Comm. Parameters. Add ("@ pagecurrent", system. Data. sqldbtype. INT). value = convert. toint32 (viewstate ["pageindex"]);

Comm. Parameters. Add ("@ fdorder", system. Data. sqldbtype. varchar, 1000). value = "";



Sqldataadapter Ada = new sqldataadapter (Comm );

Dataset DS = new dataset ();

Ada. Fill (DS, "Temp ");

This. datagrid1.datasource = Ds. Tables ["Temp"]. defaultview;

This. datagrid1.databind ();

Con. Close ();