標籤：int arch cut 主鍵 ota over from record ***
/********************************************************************************** Function: PagedProc ** Description: ** Sql2005分頁預存程序 ** 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(200), -- 表名 @ReturnFields NVARCHAR(1000) = ‘*‘, -- 需要返回的列 @PageSize INT = 10, -- 每頁記錄數 @PageIndex INT = 1, -- 當前頁碼 @Where NVARCHAR(1000) = ‘‘, -- 查詢條件 @OrderBy NVARCHAR(1000), -- 排序欄位名 最好為唯一主鍵 @PageCount INT OUTPUT, -- 頁碼總數 @RecordCount INT OUTPUT -- 記錄總數
WITH ENCRYPTION AS
--設定屬性SET NOCOUNT ON
-- 變數定義DECLARE @TotalRecord INTDECLARE @TotalPage INTDECLARE @CurrentPageSize INTDECLARE @TotalRecordForPageIndex INT
BEGIN IF @Where IS NULL SET @Where=N‘‘ -- 記錄總數 DECLARE @countSql NVARCHAR(4000) IF @RecordCount IS NULL 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=(@PageIndex-1)*@PageSize
-- 返回總頁數和總記錄數 SET @[email protected] SET @[email protected] -- 返回記錄 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 WHERE TempPageViewTable.PageView_RowNo > ‘[email protected]) ENDRETURN 0GO
sql server分頁預存程序
