22,207
社区成员
发帖
与我相关
我的任务
分享
/***************************************************************
参数说明:
1.Tables :表名称,视图,不带dbo.
2.PrimaryKey :主关键字
3.Sort :排序语句,不带Order By 比如:NewsID Desc,OrderRows Asc (Desc Asc前必须加空格)
4.CurrentPage :当前页码
5.PageSize :分页尺寸
6.Fields: 字段名
7.Filter :过滤语句,不带Where
8.Group :Group语句,不带Group By
***************************************************************/
(
@Tables varchar(200)=Null,
@Fields varchar(1000) = NULL,
@CurrentPage int = 1,
@PageSize int = 20,
@Filter varchar(1000) = NULL,
@Sort varchar(200) = NULL,
@Group varchar(200) = NULL
)
AS
BEGIN
SET NOCOUNT ON
/*默认排序*/
SET @Sort = RTRIM(LTRIM(@Sort))
IF @Fields IS NULL OR @Fields = ''
SET @Fields = '*'
DECLARE @SortTable varchar(100)
DECLARE @SortName varchar(100)
DECLARE @SQL1 varchar(4000)
/*设定排序语句.*/
DECLARE @strPageSize varchar(50)
DECLARE @strStartRow varchar(50),@strEndRow varchar(50)
DECLARE @strFilter varchar(1000)
DECLARE @strGroup varchar(1000)
/*默认当前页*/
IF @CurrentPage < 1
SET @CurrentPage = 1
/*设置分页参数.*/
SET @strPageSize = CAST(@PageSize AS varchar(50))
SET @strStartRow = CAST(((@CurrentPage - 1)*@PageSize + 1) AS varchar(50))
SET @strEndRow = CAST((@CurrentPage*@PageSize) AS varchar(50))
/*筛选以及分组语句.*/
IF @Filter IS NOT NULL AND @Filter != ''
BEGIN
SET @strFilter = ' Where ' + @Filter + ' '
END
ELSE
BEGIN
SET @strFilter = ''
END
IF @Group IS NOT NULL AND @Group != ''
BEGIN
SET @strGroup = ' GROUP BY ' + @Group + ' '
END
ELSE
BEGIN
SET @strGroup = ''
END
/*执行查询语句*/
SET @SQL1 ='select * from (Select *,ROW_NUMBER() OVER(Order By ArticleID) as row FROM ' + @Tables + ') a where row between '+@strStartRow+' and '+@strEndRow
--PRINT (@SQL1)
EXEC (@SQL1)
exec sp_executesql N'P_GetListByPage',N'@Tables varchar(200),@Fields varchar(1000),@CurrentPage int,@PageSize int,@Filter varchar(1000),@Sort varchar(200),@Group varchar(200)',@Tables=N'Article',@Fields='',@CurrentPage=1,@PageSize=20,@Filter='',@Sort='ArticleID DESC',@Group=''
exec sp_executesql N'P_GetListByPage @Tables,@Fields,@CurrentPage,@PageSize,@Filter,@Sort,@Group',N'@Tables varchar(200),@Fields varchar(1000),@CurrentPage int,@PageSize int,@Filter varchar(1000),@Sort varchar(200),@Group varchar(200)',@Tables=N'Article',@Fields='',@CurrentPage=1,@PageSize=20,@Filter='',@Sort='ArticleID DESC',@Group=''
exec P_GetListByPage @Tables=N'Article',@Fields='',@CurrentPage=1,@PageSize=20,@Filter='',@Sort='ArticleID DESC',@Group=''