|
?
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
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
