SQL paging Stored Procedure

Because the paging storage process created by the producer cannot return the total number of records, the statistics are collected again on each page. Therefore, you can add a number of returned records in the storage, which is easy to use.

@ Ipagecount int output -- Define the output variable and place it on @ querystr

....

Exec ('select' + @ fdshow + 'from' + @ querystr + @ fdorder) -- count the number of records and place them on -- if the first page is displayed...
Select @ ipagecount = @ rowcount

Call method (ASP ):

Myconstr = "driver = {SQL Server}; server = (local); uid = artme; Pwd = ***; database = artme"
Dim mycomm, userid, username
Set mycomm = server. Createobject ("ADODB. Command ")
With mycomm
. Activeconnection = myconstr 'myconstr is the database connection string
. Commandtext = "p_show" 'specifies the name of the stored procedure
. Commandtype = 4' indicates that this is a stored procedure
. Prepared = true' requires that the SQL command be compiled first
. Parameters. append. createparameter ("return", 2, 4)
. Parameters. append. createparameter ("@ ipagecount", 3, 2)
. Parameters. append. createparameter ("@ querystr", 4000, "select * From zw_language ")
. Parameters. append. createparameter ("@ pagesize", 3, 1, 4, 5)
. Parameters. append. createparameter ("@ pagecurrent", 3, 1, 4, 1)
. Parameters. append. createparameter ("@ fdshow", 4000)
. Parameters. append. createparameter ("@ fdorder", 1000)
. Execute
End
Icount = mycomm (1)
Response. Write icount

---------------------------------------------------------

-----Original Stored Procedure

Set ansi_nulls on
Set quoted_identifier on
Go

/* -- Implement generic paging stored procedures

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.
If you use the query statement and the query statement uses order by, the query statement must contain the top statement.

-- Producer build 2003.09 (reference please keep this information )--*/

/* -- Call example
Exec p_show 'select * From jobs', 5, 3

Exec p_show 'select Top 100 percent * From region data order by region name', 5, 3, 'region number, Region name, mnemonic code'
--*/
Alter proc [DBO]. [p_show]
@ Querystr nvarchar (4000), -- table name, view name, query statement
@ Pagesize Int = 10, -- size of each page (number of rows)
@ Pagecurrent Int = 1, -- the page to be displayed
@ Fdshow nvarchar (4000) = '', -- List of fields to be displayed. If you do not need to identify a field in the query result, you need to specify this value and it does not contain an ID field.
@ Fdorder nvarchar (1000) = '', -- Sort Field List
@ Des nvarchar (100) =''
As
Set nocount on
Declare @ fdname nvarchar (250) -- primary key or column name in the table or temporary table
, @ Id1 varchar (20), @ Id2 varchar (20) -- start and end record numbers
, @ 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 +'' + @ des 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 (select Top 100 percent * from '+ @ querystr + @ fdorder +')
Select '+ @ fdshow +' from # TB where '+ @ fdname + 'beten'
+ @ Id1 + 'and' + @ Id2
)