/ * - 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.' - * /
/ * Because it is necessary to take into account versatility, there is a certain amount of query statements with sorting. If you first sort, then the result is:
EXEC P_SHOW 'SELECT TOP 100 Percent * From Area Information Order By Area Name', 5, 3, 'Area Number, Region Name, Assist Code', 'Area Name'
- Query statement plus: TOP 100 percent // TOP * /
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 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 ' @ querystr @ fdorder ' SELECT ' @ fdshow ' from #tb where ' @ fdname ' BetWeen ' @ ID1 ' and ' @ ID2)
Go
