ALTER PROCEDURE [dbo].[sp_Sql_Paging]
(
@SqlDataTable NVARCHAR(4000), -- 表名
@PrimaryKey NVARCHAR(4000), -- 主鍵名稱
@Fields NVARCHAR(4000), -- 要返回的欄位
@pageSize INT, -- 頁尺寸
@pageIndex INT, -- 頁碼
@recordCount INT OUTPUT, -- 記錄總數
@strOrderBy NVARCHAR(4000), -- 排序
@strWhere NVARCHAR(4000) -- 查詢條件
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @strSQL1 NVARCHAR(4000) -- SQL語句1
DECLARE @strSQL2 NVARCHAR(4000) -- SQL語句2
DECLARE @strSQL3 NVARCHAR(4000) -- SQL語句3 SET @strSQL1 = 'SELECT ' + @PrimaryKey + ', ROW_NUMBER() OVER (' + @strOrderBy + ') AS RowNumber FROM ' + @SqlDataTable + ' ' + @strWhere --擷取總記錄數
SET @strSQL3 = 'SELECT @recordCount = COUNT(*) FROM ' + @SqlDataTable + ' ' + @strWhere
EXEC SP_EXECUTESQL
@stmt = @strSQL3,
@params = N'@recordCount AS INT OUTPUT',
@recordCount = @recordCount OUTPUT --分頁查詢
IF @pageIndex > @recordCount * 1.0 / @pageSize + 1.0 OR @recordCount <= @pageSize
BEGIN
SET @pageIndex = 1
END
SET @strSQL2 = 'SELECT ' + @Fields + ' FROM ' + @SqlDataTable + ' WHERE ' + @PrimaryKey + ' IN (SELECT '+@PrimaryKey+' FROM ('+@strSQL1+') TempTable WHERE RowNumber BETWEEN ' + Str((@pageIndex - 1) * @pageSize + 1) + ' AND ' + Str (@pageIndex * @pageSize) + ') ' + @strOrderBy
EXEC SP_EXECUTESQL @strSQL2
END |
