Paging SQL Server stored procedures

server|sqlserver| Stored Procedures | paging
/*--displays the specified table using a paging program implemented by the stored procedure, view, page X of query results for the primary key or identity column in the table, fetch the query directly from the original table, otherwise use the temporary table method if there are primary keys in the view or query results, this method is not recommended--Jiangjian 2003.09--*//*-- Call the example EXEC p_show ' area information ' exec p_show ' area information ', 5, 3, ' region number, region name, mnemonic code ', ' area number '--*//* because of the universality, there is a certain requirement for the query with ordering. If you sort first, then the result. EXEC p_show ' Select top percent * from area data order by region name ', 5, 3, ' region number, region name, mnemonic code ', ' Region name '--query statement plus: Top percent//top 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] Gocreate Proc p_show@querystr nvarchar (4000),--table name, view name, query statement @pagesize int=10,--the size (number of rows) per page @PageCurrent int=1,-- The page to display @fdshow nvarchar (4000) = ',--the list of fields to display, and if the query results have a literacy segment, you need to specify this value and do not include an identity field @fdorder nvarchar (1000) = '--sorted field list Asdeclare @FdName nvarchar (250)-The name of the identity column in the primary key or table in the table, in the temporary table, @Id1 varchar, @Id2 varchar (20)-The record number of the start and end @Obj_ID int--Object id--table with composite primary key processing declare @strfd nvarchar (2000)--composite primary key list, @strjoin nvarchar (4000)--join field, @strwhere nvarchar (2000)- -Query Criteria 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 end, @Qu Erystr=case when @Obj_ID are not null then ' + @QueryStr Else ' (' + @QueryStr + ') A ' end--if the first page is displayed, you can complete the if @PageCurrent by using top directly 1beginselect @Id1 =cast (@PageSize as varchar) EXEC (' select top ' + @Id1 + @FdShow + ' from ' + @QueryStr + @FdOrder) returnend--if it is a table, check to see if the table has identity or primary key if @Obj_ID is not null and OBJECTPROPERTY (@Obj_ID, ' istable ') =1beginselect @Id1 =cast (@ PageSize as varchar), @Id2 =cast ((@PageCurrent-1) * @PageSize as varchar) Select @FdName =name from syscolumns where Id= @Obj_ID and status=0x80if @ @rowcount =0--If there are no identity columns in the table, check the table for a primary key beginif not EXISTS (select 1 from sysobjects where parent_ obj= @Obj_ID and xtype= ' PK ') goto lbusetemp--If there is no primary key in the table, the select @FdName is processed with a temporary table =name from syscolumns where id= @Obj_ID and colid In (the select Colid from Sysindexkeys where @Obj_ID =id and indid in (select Indid to 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 beginselect @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 to Sysindexkeys where @Obj_ID =id and indid in (select IND ID from sysindexes where @Obj_ID =id and name in (select name from sysobjects where xtype= ' PK ' and parent_obj= @Obj_ID)) Sele CT @strfd =substring (@strfd, 2,2000), @strjoin =substring (@strjoin, 5,4000), @strwhere =substring (@strwhere, 5,4000) Goto Lbusepkendendendelsegoto lbusetemp/*--Use an identity column or primary key for a single field processing--*/lbuseidentity:exec (' select top ' + @Id1 + @FdShow + ' From ' + @QueryStr + ' where ' + @FdName + ' isn't in (select top ' + @Id2 + ' + @FdName + ' from ' + @QueryStr + @FdOrder + ') ' + @FdOrder) The processing method for a composite primary key in the return/*--table--*/lbusepk:exec (' select ' + @FdShow + ' from (select top ' + @Id1 + ' a.* ') (select top Percent * fro M ' + @QueryStr + @FdOrder + ') aleft join (select top ' + @Id2 + ' + @strfd + ' "from ' + @QueryStr + @FdOrder + ') b on ' + @strjoin + ' where ' + @strwhere + ') return/*--method--*/lbusetemp:select with temporary table @FdName = ' [ Id_ ' +cast (NEWID () as varchar) + '] ', @Id1 =cast (@PageSize * (@PageCurrent-1) as varchar), @Id2 =cast (@PageSize *@ PageCurrent-1 as varchar) EXEC (' select ' + @FdName + ' =identity (int,0,1), ' + @FdShow + ' into #tb from ' + @QueryStr +@ fdorder+ ' SELECT ' + @FdShow + ' from #tb where ' + @FdName + ' between ' + @Id1 + ' and ' + @Id2] Go