|
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
|
