/********************************************************************************** function:pagedproc * * Description: * * Sql2005 paging stored procedure * * Finish DateTime: * * 2009/1/3 ** Example: ** Web_pageview @Tablename = ' Table1 ', @Returnfields = ' * ', ** @PageSize = 2, @ PageIndex = 1, @Where = ', ** @OrderBy =n ' ORDER by id Desc ' **********************************************************************************/
IF EXISTS (SELECT * from DBO. SYSOBJECTS WHERE ID = object_id (N ' [dbo].[ Pagedproc] ') and OBJECTPROPERTY (ID, N ' isprocedure ') = 1) DROP PROCEDURE [dbo]. [Pagedproc] GO
SET QUOTED_IDENTIFIER Ongo
SET ANSI_NULLS on GO
CREATE PROCEDURE dbo. pagedproc @TableName nvarchar (, ) --table name @ReturnFields NVARCHAR (+) = ' * ', --need to return columns @PageSize int = 10, - -Number of records per page @PageIndex int = 1, --Current Page @Where nvarchar (+) = ", --query criteria @OrderBy nvarchar (, ) --sort field names are best for unique primary keys @PageCount int output, --page Count @RecordCount int output --Total Records
With encryption as
--Set NOCOUNT on
--Variable definition declare @TotalRecord intdeclare @TotalPage intdeclare @CurrentPageSize intdeclare @TotalRecordForPageIndex INT
BEGIN IF @Where is NULL SET @Where =n "--Total records DECLARE @countSql NVARCHAR (4000) if @RecordCount is NU LL BEGIN SET @countSql = ' SELECT @TotalRecord =count (*) from ' [Email protected]+ ' [email protected] EXECUTE sp_executesql @countSql, N ' @TotalRecord int out ', @TotalRecord out END ELSE BEGIN SET @[email protected] END set @[email protected] Set @TotalPage = (@TotalRecord-1)/@PageSize +1 SET @CurrentPageSize = (@PageInde X-1) * @PageSize
--returns the total number of pages and total records set @[email protected] SET @[email protected] --return records set @[email protected]* @PageSize exec (' SELECT * from ( SELECT TOP ' [email protected]+ ' [email protected]+ ', Row_number () over (' [email protected]+ ') as Pageview_rowno from ' [email protected]+ ' + @Where + ') as temppageviewtable & nbsp WHERE Temppageviewtable.pageview_rowno > ' [email protected] endreturn 0go
SQL Server paging stored procedures
