ASP call example:
http://blog.9cbs.net/baikaishui_0825/archive/2004/12/10/211450.aspx
Repost
-------------------
/ * - Universal stored procedure for implementing paging
Display page X of the specified table, view, and query results
For the main key or identity column in the table, take the query from the original table, other cases of using a temporary table
This method is not recommended if there is a primary key in the view or query result.
If you use a query statement, and the query statement uses the order by, the query statement must contain top statements.
- Thank 9cbs.Net Zou Jian - * /
/ * - Call example
EXEC P_SHOW 'Area Information'
Exec P_show 'SELECT TOP 100 Percent * From Area Profile ORDER BY Region Name', 5, 3, 'Area Number, Region Name, Assist Code'
- * /
Create Proc P_show
@Querystr nvarchar (4000), - Table name, view name, query statement
@PageSize Int = 20, - Size per page (number of lines)
@PAGECURRENT INT = 1, - Page to display
@FDSHOW NVARCHAR (4000) = '', - the list of fields to display, if the query result does not need to identify the field, you need to specify this value without the identification field
@Fdorder nvarchar (1000) = '- List of Sort Fields
AS
Set nocount on
Declare @fdname nvarchar (250) - The primary key or table in the table, the identity column name in the temporary table
, @ Id1 varchar (20), @ id2 varchar (20) - start and end record number
, @ Obj_id int-- Object ID
- Treatment of composite primary keys in the table
Declare @strfd nvarchar (2000) - Composite primary key list
, @ strjoin nvarchar (4000) - Connection field
, @ Strwhere Nvarchar (2000) - Query Conditions
SELECT @ obj_id = Object_id (@querystr)
, @ Fdshow = case isnull (@fdshow, '') when ''1' * 'else' ' @ fdshow end
, @ Fdorder = case isnull (@fdorder, '') when '' Ten '' else 'Order by' @ fdorder End
, @ Querystr = case when @obj_id is not null dam t '' @ querystr else '(' @ querystr ') a' end
- If the first page is displayed, you can use TOP to complete
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 if there is a logo or primary key in the table.
IF @obj_id is not null and objectproperty (@ obj_id, 'istable') = 1begin
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 there is no marking column in the table, check the table has a primary key
Begin
IF not exists (SELECT 1 from sysoads where parent_obj = @ obj_id and xtype = 'pk')
Goto lbuseTemp - If there is no primary key in the table, use a temporary table.
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 pient_obj = @ Obj_id
)))))))
IF @@ rowcount> 1 - Check if 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 syscolumn 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 pient_obj = @ Obj_id
)))))))
SELECT @ strfd = substring (@ strfd, 2,2000)
@ Strjoin = Substring (@ Strjoin, 5, 4000)
, @ Strwhere = Substring (@ Strwhere, 5, 4000)
Goto lbusepk
end
end
end
Else
Goto lbuseTemp
/ * - Use the identity column or the primary key for a single field - * / lbuseidentity:
EXEC ('SELECT TOP' @ ID1 @ fdshow 'from' @ querystr
'Where' @ fdname 'Not in (SELECT TOP'
@ ID2 '' @ fdname 'from' @ querystr @ fdorder
')' @ Fdorder
)
Return
/ * - Treatment method for composite primary keys in the table - * /
lbusepk:
EXEC ('SELECT' @ fdshow 'from (SELECT TOP' @ ID1 a. * from
(SELECT TOP 100 Percent * from ' @ querystr @ fdorder ') a
Left Join (SELECT TOP ' @ ID2 ' @ strfd "
From ' @ querystr @ fdorder ') b on ' @ Strjoin '
Where ' @ Strwhere ') a '
)
Return
/ * - Method for processing 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 = Id, 0, 1), ' @ fdshow '
INTO #TB from ' @ querystr @ fdorder '
SELECT ' @ fdshow ' from #tb where ' @ fdname ' Between '
@ Id1 'and' @ id2
)
Go
Giant fast, universal table maintenance increases to change the storage process paging display
(Disease name, nearly 20,000 data)
Export Excel and download
Http://www.adr.gov.cn/download/panyuguang/adrnew/dict_list.asp
download
FTP: // 9cbs: 9cbs@61.152.210.28/
Changed a space, no way, hit the space everywhere.
- Get the data of the specified page
Create Procedure GetRecordfromPage
@tblname varchar (255), - Table name
@fldname varchar (255), - field name
@PageSize INT = 10, - Page Size @PageIndex INT = 1, - Page
@Iscount bit = 0, - Return to the total number of records, non-0 values return
@ORDERTYPE bit = 0, - Set the sort type, descending order of non-0 value
@Strwhere Varchar (1000) = '' - Query Conditions (Note: Don't add WHERE)
AS
Declare @strsql varchar (6000) - main statement
Declare @strtmp varchar (100) - temporary variable
Declare @strorder varchar (400) - 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
IF @iscount! = 0
Set @strsql = "Select count (*) as total from [" @TBLNAME "]"
EXEC (@strsql)
Go
[Millions] Universal stored procedures. Paging stored procedures.
/ *
Name: Spall_returnRows
enter:
Output:
transfer:
Exec Spall_returnrows' SELECT * FROM table name, page number, return record number, 'primary key', 'sort field'
Spall_returnrows' Select * from all_categories', 2, 10, '[id],' [id] '
Description: [Million] Universal stored procedure. Page stored procedure .. Returns the number of specified returns, specifying the number of pages
Author: Dili J.F. Senders
Email: diliatwellknow.net
Website: http://www.wellknow.net
Update: 20040610
Support: http://bbs.wellknow.net
Copyright: Please indicate the source: Create future WellkNow.Net with thinking
* /
Create Procedure DBo.spall_ReturnRows
(
@Sql nvarchar (4000),
@Page Int,
@RecsperPage Int,
@ID varchar (255),
@Sort varchar (255)
)
AS
Declare @str nvarchar (4000)
Set @ Str = 'SELECT TOP' CAST (@Recsperpage As Varchar (20)) '* from (' @ SQL ') T where t.' @id NOT IN
(SELECT TOP ' CAST ((@ recsperpage *) AS varchar (20)) ' ' @ ID ' from (' @ SQL ') T9 Order By ' @ Sort ') Order by ' @ Sortprint @str
EXEC SP_EXECUTESQL @str
Go
