The most basic processing method:
If there is a primary key in the table (can be recorded without repeated fields), you can use the following method, of course, X, Y is replaced with specific numbers, and the variables cannot be used:
SELECT TOP Y *FOM table Where master key NOT IN (SELECT TOP (X-1) * Y master key from table)
If there is no primary key in the table, you can use a temporary table, add the identification field. The x, y can use variables.
Select ID = Identity (int, 1, 1), * INTO #TB from WHERE ID BETWEEN (X-1) * y and x * Y-1
- Improve the general process of implementing the stored procedure.
/ * - Paging program implemented with stored procedures
Displaying pages X page of the specified query results This stored method for the non-primary key, if there is a primary key, this method is not recommended.
Zou Jian 2003.09 - * /
/ * - Call example exec p_show 'select * from xzkh_new .. area information'
EXEC P_SHOW 'SELECT * from Xzkh_new .. Regional Information', 5, 2, 'Area No., Region Name, Assist Code', 'Area No.' - * /
IF exists (select * from dbo.sysObjects where id = Object_id (n '[dbo]) And ObjectProperty (ID, n'isprocedure') = 1) Drop Procedure [dbo]. [p_show] Go
Create Proc P_SHOW @ querystr nvarchar (1000), - query statement, if the query table, use: select * from the table @PageSize Int = 10, - Size per page (number of lines) @PageCurrent int = 1, Displayed page @fdshow nvarchar (1000) = '' ,- To display the list of fields, if the query result is identified by the identity field, you need to specify this value without including the identification field @fdorder nvarchar (1000) = '' - sort Field List AsDeclare @fdname Nvarchar (50) - Identification Column name, @ id1 varchar (20), @ id2 varchar (20) - Start and end record number select @fdname = '[id _' cast (newID () As varchar (40)) '], @ ID1 = CAST (@PageSize * (@ PAGECURRENT-1) as varchar (20)), @ id2 = cast (@ PageSize * @ PageCurrent-1 as varchar (20)) , @ Fdshow = case isnull (@fdshow, '') when '' Ten '*' else @fdshow end, @ fdorder = Case isnull (@fdorder, '') when '' Ten 'Else' Order By ' @ FDORDER END
Exec ('SELECT' @ fdname = Id, 0, 1), ' @ fdshow ' Into #tb from (' @ querystr ') a ' @ fdorder ' SELECT ' @ fdshow ' from #TB Where ' @ id1 ' and ' @ ID2) Go - The above only applies to SQL queries, not suitable for tables, views (to be written as a SELECT * FROM table), the following is more perfect More complex.
/ * - Paging program implemented with stored procedures
Display the status of the specified table, view, query results For the case of the primary key or identity column in the table, directly from the original table, other cases use a temporary table if there is a primary key in the view or query results, this method is not recommended
Zou Jian 2003.09 - * /
/ * - Call example EXEC P_SHOW 'Area Information'
EXEC P_SHOW 'Regional Information', 5, 3, 'Area No., Region Name, Assisted Decod', 'Area No.' - * /
IF exists (select * from dbo.sysObjects where id = Object_id (n '[dbo]) And ObjectProperty (ID, n'isprocedure') = 1) Drop Procedure [dbo]. [p_show] Go
CREATE PROC P_SHOW @ querystr nvarchar (4000), - Table name, view name, query statement @PageSize int = 10, - Size of per page (number of lines) @PAGECURRENT INT = 1, - Page to display @fdshow NVARCHAR (4000) = '' ,- To display the list of fields, if the query result is identified by identification field, you need to specify this value without including the identification field @fdorder nvarchar (1000) = '' - Sort field list asdeclare @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 - Table with composite primary keys 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 '' '*' else '' @ fdshow end, @ fdorder = case isnull (@fdorder, '') When '' '' Else 'Order By' @ fdorder end, @ querystr = case when @obj_id is not null damj ' @ 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) Returnend
- If it is a table, check if there is a logo or the primary key 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 in the table, check the table with the primary key Begin if not exists in the table (SELECT 1 from sysobjects where parent_obj = @Obj_id and xtype = 'pk') goto lbuseTemp - If there is no primary key in the table, use 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 PARENT_OBJ = @ Obj_id))) IF @@ rowcount> 1 - Check if the primary key in the table is a composite primary key becom selection @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 (@ strjoin = Substring (@ strjoin, 5,4000), @ Strwhere = Substring (@ Strwhere, 5,4000) Goto lbuseTemp / * - How to use identity columns or primary keys 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 ') Return / * - Method for treating temporary table - * / lbuseTemp: select @fdname = '[id _' casket (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
Supplementary instructions, for the case of using the query, if the query statement contains the order by the order, you must use the following query, you must include top: select top 100 percent * from table Order By field Oracle I haven't used it. It should be ok.
I heard that Oracle has a function such as RowID (), you can get the line number, you can not use it so trouble.
In the program, it is best to implement the Paging function of ADO.
'** ----- Database connection string template ----------------------------------- - '** access database' ** iconcstr = "provider = microsoft.jet.OLDB.4.0; persist security info = false" & _ '** "password =" "Password" "; data source = database name"' ** '** SQL Database' ** iconcstr = "Provider = SQLOLEDB.1; PERSIST Security Info = True;" & _ '** "User ID = User Name; Password = Password; Initial Catalog = Database Name; Data Source = SQL server name "
Paging example in VB, as an example of Access database, SQL database only needs to change the connection string 'reference: Microsoft ActiveX Data Objects 2.x library'2.x is the version number sub split () Dim Ire as adoDb.recordset Dim Iconc As String, ICOUNT &, II &, IJ & ICONC = "Province = Microsoft.jet.OLDB.4.0; PERSIST Security Info = FALSE" & _ "; Data Source = f: / my documents / customer information .mdb" set IRE = New ADODB.Recordset With iRe .CursorLocation = adUseClient .Open "customer", iConc, adOpenKeyset, adLockOptimistic .PageSize = 10 iCount = .PageCount For iI = 1 To iCount .AbsolutePage = iI For iJ = 1 To .PageSize Debug.Print .Fields (1) .MOVENEXT IF .EOF THEN EXIT for Next NEXT END with IRE.CLOSE SET IRE = Noth Ind Sub'ASP Ped Power As an example, Access database only needs to change the connection string <% iconc = "provike = SQLOLEDB.1; persist security info = true; user ID = username; password = password; initial catalog = database name; data source = sql server name "set IRE = Server.createObject (" adoDb.recordset ") with IRE? ?????? .cu RsorLocation = aduseclient ??????? .open "Table name", iconc, 1, 1 ??????? .pagesize = 10 ??????? 'per page size ????? ?? iCount = .pagecount ?? 'total pages ???????? .Absolute = 2 ????' Set the current display, here is page 2 ??????? for ij = 1 TO .PAGESIZE? 'Loop display the current page record ??????????????' This changed to the display processing code ??????????????? .MOVENEXT ???????????????? f .eof kil dam ??????? NEXT ??? End with ??? ??? Ire.close ??? set IRE = Nothing%>
- Example Processing:
- Data test environment
- Check if the object exists, if there is, delete if EXISTS (Select * from dbo.sysObjects where id = Object_id (n '[primary table]') And ObjectProperty (id, n'uSERTABLE ') = 1) Drop Table [ Table] Goif EXISTS (Select * from dbo.sysObjects where id = Object_id (n '[slave table]') And ObjectProperty (id, n'uSERTABLE ') = 1) DROP TABLE [From Table] Go
IF exists (select * from dbo.sysObjects where id = Object_id (n '[dbo]. [p_qry]') And ObjectProperty (ID, n'isprocedure ') = 1) Drop Procedure [dbo]. [p_qry] Go
- Creating a table environment Create Table main table (id varchar (40) Not Null Constraint PK_işmek Primary Key, CreateDate DateTime)
Create Table from the table (ID varchar (40) Not null, indexid varchar (40) NOT NULL, VALUE VARCHAR (500)) ALTER TABLE PK_ From Table PRIMARY CLUSTERED (ID, INDEXID)
- Insert Test Data INSERT INTO Stem SELECT '01', '2003-10-21' ENION All SELECT '02', '2003-10-21'
INSERT INTO From Table Select '01', '1001', 'ABY6', 'Abcd'Union All Select' 01 ',' 1003 ',' JSFK'Union All Select '01' , '1T0B', 'YUET'UNION All SELECT' 02 ',' 1001 ',' JDJD'Union All Select '02', '1002', 'Ksks'Union All Select' 02 ',' Aby6 ',' Hyei '
GO - Creating a Paging Query Create Proc P_QRY @ WHERE VARCHAR (8000) = '', - The condition of the query belongs to the field of the primary table with a. field, belongs to the field from the table with b. field @PageSize Int = 20, - The size @Page INT = 1 per page, - To query the first few pages @createView bit = 1 - whether to rebuild the view, the first call or the query condition is specified as 1, other cases designated 0asDeclare @SQL varchar (8000) declare @VIEWNAME SYSNAMESET @ViewName = 'tmp_qry _' host_name () "username" User's computer name login user name, view name if Object_id (@VIEWNAME) is null goto lb_createviewelse if @ createview = 1begin set @ sql = 'drop view [' @ viewname ']' exec (@sql) goto lb_createviewendgoto lb_qrylb_createview: if @where <> '' set @ where = 'where (' @ WHERE ')' EXEC ('Create View [' @ ViewName '] As SELECT A. *, B.indexid, B.Value from primary table A inner Join from table b on a.id=b.id' @where )
LB_QRY: DECLARE @ p1 varchar (20), @ p2 varchar (20) if @ Page = 1begin set @ P1 = CAST (@Pagesize as varchar) EXEC ('SELECT TOP' @ P1 '* from [' @ viewname " ] ') endelsebegin select @ p1 = cast (@Pagesize as var), @ p2 = cast ((@ Page-1) * @ PageSize as varchar) EXEC (' SELECT TOP ' @ P1 ' * from [' @ viewname '] a left join (SELECT TOP' @ P2 'ID, INDEXID FROM [' @ viewname ']) b on a.id = B.ID and a.indexid = B.Indexid WHERE B.ID is NULL') Endgo
- Call Test EXEC P_QRY @where = '', @ PageSize = 5, @pa
EXEC P_QRY @where = ', @ Pagesize = 5, @ Page = 1, @ createview = 0 - When the query condition is not changed, the view is not created again.
- Conditional call EXEC P_QRY @ where = 'B.indexid =' '1002' 'and b.Value Like' '% ABCD%' '' and a.createdate between '' 2003-10-21 '' and '' 2003-11-21 '', @ PageSize = 5, @ Page = 2exec p_qry @ PageSize = 5, @ Page = 1, @ createView = 0 - Second call, so do not need to write conditions, only need to set To call the first few pages. However, if there is a primary key in the table, and in the program, or use the ADO paging, or use the method of remember the maximum primary key / minimum primary key of this query.
/ *** Method One is completely conforming to the meaning, you can retrieve all attributes that meet the IDs given the indicator condition ** /
--- The following starts establishing a test environment:
Create Table T1 (id varchar (10), createDate datetime) INSERT T1 SELECT '01', '2003-10-21'Union All SELECT' 02 ',' 2003-10-21 '
Crext Table T2 (ID VARCHAR (10), INDEXID VARCHAR (10), Value Varchar (10)) Insert T2 Select '01', '1001', 'ASDF'Union All Select' 01 ',' 1002 ',' ABCD ' Union all select '01', '1003', 'JSFK'Union All Select' 01 ',' 1T0B ',' YUET'UNION All Select '02', '1001', 'ASDF'Union All Select' 02 ',' 1002 ',' Abcd'Union All Select '02', '1008', 'Hijk' - Establishing Process (Normal Edition) CREATE PROC Process @ 号 INT, @ per page size int, @ Start Time VARCHAR (10), @ End Time VARCHAR (10), @ 指标 条 v VARCHAR (8000) ASDECLARE @A varchar (8000), @ b varchar (8000) set @ a = 'select Identity (int, 1, 1) MID, a. *, B .indexid, B. Value Into # from t1 a join t2 b on a.id = B.ID where a.createdate betWeen '' ' 开始 时时 ' 'and' ' @ End Time ' '' And not exists (select 1 from ('set @ b =') TEM where not exists (SELECT 1 from t2 where indexid = tem.indexid and value = tem.value and id = a.id)) SELECT TOP ' CAST @ 每 小 a as varchar (10)) 'id, created, indexid, value from # where mid> (' cast (@P Page number as varchar (10)) '- 1) *' Cast (@ Page size as varchar (10)) EXEC (@ a @ index condition @ b) Go - call:
- Get the first page, 2 records per page, start time and end time yourself, pay attention to the following indicator conditions, more troubles of the EXEC process 2, 2, '1900-1-1,' 2003-10-30 ', 'SELECT' '' 'INDEXID,' 'ASDF' 'Value Union All Select' '1003', '' JSFK '' '
- Test 2: EXEC processes 1, 3, '1900-1-1', '2003-10-30', 'SELECT' '' '' 'INDEXID,' 'ASDF' 'Value Union All Select' '1003' ' , '' jsfk '' '------------------------------------------- -------------------------------------------------- -------------------------------------------------- ---------------------------------------
--- Here is the optimization input condition (simplified input version)
--Drop Proc Process
Create Proc Process @ 号 INT, @ 每 大 小 (10), @ End Time Varchar (10), @ 指 条 条 v VARCHAR (1000) Asdeclare @A varchar (8000), @ b varchar (8000 ), @ c varchar (8000)
Set @ c = 'select' '' replace (@ 指标 条 条, ';', '' ''), ',', '' 'a,' ') ' ' '' set @ a = 'Select Identity (int, 1, 1) MID, A. *, B.indexid, B.Value Into # from t1 a join t2 b on a.id = B.ID where a.createdate betWeen '' ' @ Start Time ' '' and '' ' @ End Time ' '' and NOT EXISTS (SELECT 1 from ('set @ b =') Tem where not exists (SELECT 1 from t2 where indexid = Tem.a and value = tem.b and id = a.id)) SELECT TOP ' CAST (@ 每 小 大 AS varchar (10)) ' ID, CreateDate, Indexid, Value from # where mid> (' Cast (@P Page Number As Varchar (10)) '- 1) *' Cast (@ 每 大 size as varchar (10)) EXEC (@ a @ c @ b) Go
- Get the first page, 2 records per page, start time, and end time yourself, pay attention to the back indicator conditional format is the number division column; the number of EXEC processes 1, 2, '1900-1-1', '2003-10-30', '1001, ASDF; 1003, JSFK'
- Test 2: EXEC processes 2, 3, '1900-1-1', '2003-10-30', '1001, ASDF; 1003, JSFK'
-------------------------------------------------- --------------------------------------- / *** Real Use Change Note:
Set @ a = 'SELECT IDENTITY (INT, 1, 1) MID, A. *, B.indexid, B.Value Into # from t1 a join t2 b on a.id = B.ID where a.createdate betWeen' ' ' @ Start Time ' '' '' ' @ End Time ' '' and NOTENTINTS (SELECT 1 from: set @ a = 'SELECT IDENTITY (Int, 1, 1) MID, A *, b.indexid, b.Value << ==== Here the main building must be adjusted to the column name of the actual operation, and do not repeat the column name, such as: a. list 1, a. column 2, b. list 1 AS alias << == This alias is used to prevent heavy names
If the change is large, you can debug the following method: During: Exec (@ a @ c @ b) temporarily change to: print (@ a @ c @ b) then put the code out of the print in another query analyzer Check if it is the same as pre-envision
If you have any questions or don't change it to actual use: you can use SMS or pengdali@hotmail.com**//** optimization scheme
It is optimized for the simplified input version of the above
** /
--Drop Proc Process
Create Proc Process @ 号 INT, @ 每 大 小 (10), @ End Time Varchar (10), @ 指 条 条 v VARCHAR (1000) Asdeclare @A varchar (8000), @ b varchar (8000 ), @ c = 'select' '' replace (Replace (@ index condition, ';', '' ')' '),', ',' '' , '') '' '' set @ a = 'SELECT IDENTITY (INT, 1, 1) MID, AA. *, Bb.indexid, Bb.Value Into # from (SELECT * from T1 Where CreateDate Between' " ' @ Start Time ' '' and '' ' @ End Time ' '' and NOTSTS (SELECT 1 from ('set @ b =') Tem Where Not Exists (SELECT 1 from t2 where indexid = TEM. A and value = tem.b and id = t1.id))) AA JOIN T2 BB on aa.id = bb.id SELECT TOP ' CAST (@ Each size as varchar (10)) ' ID, Createdate, Indexid, Value from # Where Mid> (' CAST (@ Page Number As Varchar (10)) ' - 1) * ' Cast (@ 每 大 size as varchar (10)) EXEC (@ A @ C @ B ) GO - get the first page, 2 records per page, start time, and end time yourself, pay attention to the back indicator conditional format is, number division column; number of rows of EXEC processes 1, 2, '1900-1-1 ',' 2003-10-30 ',' 1001, ASDF; 1003, JSFK '
- Test 2: EXEC processes 2, 3, '1900-1-1', '2003-10-30', '1001, ASDF; 1003, JSFK'
- Optimized object is a statement, such a performance can be improved. - You can also establish an index for your INDEXID column and the value column and ID column of your slave table. CREATE INDEX slave _value_index on slave table (ID) * /
/ * Paging program implemented with stored procedures * / create proc sp_pageRecordset @querystr nvarchar (1000), - Query statement, do not add "SELECT" or "Top N" @keyfield nvarchar (200), - Identification field @ PageSize Int, - Row of per page @PAGENUMBER INT - Page number to display, start from 0 asbegin declare @sqltext as nvarchar (4000) Declare @sqltable as nvarchar (4000) set @sqltable = 'select top' cast ((@PAGENUMBER 1) * @PageSize As Varchar (30)) '' @querystr set @sqltext = 'SELECT TOP' CAST (@PageSize As Varchar (30)) '*' 'from (' @sqltable ') as Tablea' 'Where' @keyfield 'Not in (SELECT TOP' CAST (@PAGENUMBER * @Pagesize As Varchar (30)) '' @keyfield 'from (' @Sqltable ') as Tableb)' Exec (@Sqltext) end --------------------------------- ------------------------ Drop Procedure SP_PAGERECORDSET
EXEC SP_PAGERECORDSET @querystr = '* from wzta order by [id]', @keyfield = '[id]', @PageSize = 100, @PAGenumber = 6000
Related connections:
Http://search.9cbs.net/expert/topic/2365/2365596.xml?temp=.3725092
