- CREATE PROCEDURE [dbo].[GetPagingList](
- @Table nvarchar(1000), --表名
- @Field nvarchar(1000) = '*', --读取字段
- @Where nvarchar(500) = NULL, --Where条件
- @GroupBy nvarchar(500) = NULL, --分组
- @OrderBy nvarchar(500)= NULL, --排序字段
- @PrimaryKeyField nvarchar(50), --主键必需
- @PageNumber int = 1, --开始页码
- @PageSize int = 10, --页大小
- @IsCount bit = 0 --是否返回记录总数
- )
- AS
- BEGIN
- ------------------------------------------------------------------------------------------------
- DECLARE @strWhere nvarchar(500) --Where 条件
- IF @Where IS NOT NULL AND @Where != '' --Where 条件
- BEGIN
- SET @strWhere = ' WHERE ' + @Where + ' '
- END
- ELSE
- BEGIN
- SET @strWhere = ''
- END
- ----------------------------------------------------------------------------------------------------
- DECLARE @strGroupBy nvarchar(500) --GroupBy 条件
- IF @GroupBy IS NOT NULL AND @GroupBy != '' --GroupBy 条件
- BEGIN
- SET @strGroupBy = ' GROUP BY ' + @GroupBy + ' '
- END
- ELSE
- BEGIN
- SET @strGroupBy = ''
- END
- ----------------------------------------------------------------------------------------------------
- DECLARE @strOrderBy nvarchar(500) --OrderBy 条件
- IF @OrderBy IS NULL OR @OrderBy = '' --OrderBy 条件
- BEGIN
- SET @strOrderBy = ' ORDER BY ' + @PrimaryKeyField + ' DESC'
- END
- ELSE
- BEGIN
- SET @strOrderBy = ' ORDER BY ' + @OrderBy
- END
- ----------------------------------------------------------------------------------------------------
- DECLARE @strSql nvarchar(max) --Sql 语句
- --计算总行数
- IF @IsCount = 1
- BEGIN
- SET @strSql= 'SELECT Count (*) AS RecordCount FROM ' + @Table + @strWhere + @strGroupBy
- EXEC sp_executesql @strSql
- RETURN
- END
- ----------------------------------------------------------------------------------------------------
- IF @PageNumber < 1 --第一页提高性能
- BEGIN
- SET @PageNumber = 1
- END
- IF @PageNumber = 1
- BEGIN
- SET @strSql = 'SELECT TOP ' + str(@PageSize) + ' ' + @Field + ' FROM ' + @Table +
- @strWhere + @strGroupBy + @strOrderBy
- EXEC sp_executesql @strSql
- RETURN
- END
- ----------------------------------------------------------------------------------------------------
- --根据 SqlServer 2005 帮助得到下面的语句
- DECLARE @STARTID nvarchar(50)
- DECLARE @ENDID nvarchar(50)
- SET @STARTID = convert(nvarchar(50),(@PageNumber - 1) * @PageSize + 1)
- SET @ENDID = convert(nvarchar(50),@PageNumber * @PageSize)
- SET @strSql = 'WITH MYTABLE AS (SELECT ROW_NUMBER() OVER (' + @strOrderBy + ')
- AS RowNumber,' + @Field + ' FROM '+ @Table + @strWhere + @strGroupBy + ')
- SELECT * FROM MYTABLE
- WHERE RowNumber BETWEEN ' + @STARTID + ' AND ' + @ENDID
- EXEC sp_executesql @strSql
- --------------------------------------------------------------------------------------------------
- END
Sql2005 版通用分页存储过程
最新推荐文章于 2024-08-13 14:34:13 发布