Transfer from: http://blog.9cbs.Net/mw248/archive/2006/06/21/818975.aspx
Zou Jian: 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
/ * - Universal stored procedure for implementing paging
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 If you use a query statement, and the query statement uses the order by, the query statement must contain top statements.
- Zou Jian 2003.09 (Please keep this information) - * /
/ * - Call example EXEC P_SHOW 'SELECT * from Jobs', 5, 3
Exec P_show 'SELECT TOP 100 Percent * From Area Profile ORDER BY Area Name', 5, 3, 'Area Number, Region Name, Assist Code' - * / CREATE P_SHOW @ querystr nvarchar (4000), - Table name , View name, query statement @PageSize INT = 10, - size per page (number of lines) @PAgeCurrent int = 1, - Page @fdshow nvarchar (4000) = '', - To display field List, if the query result does not require the identity field, you need to specify this value without including the identification field @fdorder nvarchar (1000) = '' - Sort field list asset nocount ondeclare @fdname nvarchar (250) - the primary key in the table or Table, Identification column name in the temporary table, @ id1 varchar (20), @ id2 varchar (20) - start and end record number, @ obj_id int - Object ID - Table has a composite primary key 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 '' '*' 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 objectprpRoperty (@ 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 (select top 100 percent * from ' @ querystr @ fdorder a select' @ fdshow " From #TB where ' @ fdname ' between ' @ id1 ' and ' @ id2)
Go
