我想实现一个分页的存储过程,并可以按输入的参数进行排序
可是总是不知道哪儿出了问题,不能实现想要的效果下面是代码,虽然有点长但节构很简单啊,核心的就几句,
烦请各位好心高手花一点点时间指导一下~~谢谢啊!!ALTER PROCEDURE [dbo].[Topic_Paging]
@PageSize int,
@CurrentPage int,
@SpecialId int, -- Special与Topic表是一对多的关系
@WayId int -- 排序方式
AS
DECLARE @FirstNumber int, @LastNumber int
SET @FirstNumber = (@CurrentPage-1) * @PageSize + 1
SET @LastNumber = @FirstNumber + @PageSize - 1
BEGIN IF (@WayId = 0 OR @WayId IS NULL)
SELECT * FROM(
SELECT *, Row_Number() OVER(ORDER BY [TopicId] DESC) AS RowNumber FROM [Topic]
) AS A
WHERE
([SpecialId] = @SpecialId OR @SpecialId IS NULL)
AND
(RowNumber BETWEEN @FirstNumber AND @LastNumber)
ELSE IF (@WayId = 1)
SELECT * FROM(
SELECT *, Row_Number() OVER(ORDER BY [Click] DESC) AS RowNumber FROM [Topic]
) AS A
WHERE
([SpecialId] = @SpecialId OR @SpecialId IS NULL)
AND
(RowNumber BETWEEN @FirstNumber AND @LastNumber)
ELSE IF ....
SET NOCOUNT ON;
END
可是总是不知道哪儿出了问题,不能实现想要的效果下面是代码,虽然有点长但节构很简单啊,核心的就几句,
烦请各位好心高手花一点点时间指导一下~~谢谢啊!!ALTER PROCEDURE [dbo].[Topic_Paging]
@PageSize int,
@CurrentPage int,
@SpecialId int, -- Special与Topic表是一对多的关系
@WayId int -- 排序方式
AS
DECLARE @FirstNumber int, @LastNumber int
SET @FirstNumber = (@CurrentPage-1) * @PageSize + 1
SET @LastNumber = @FirstNumber + @PageSize - 1
BEGIN IF (@WayId = 0 OR @WayId IS NULL)
SELECT * FROM(
SELECT *, Row_Number() OVER(ORDER BY [TopicId] DESC) AS RowNumber FROM [Topic]
) AS A
WHERE
([SpecialId] = @SpecialId OR @SpecialId IS NULL)
AND
(RowNumber BETWEEN @FirstNumber AND @LastNumber)
ELSE IF (@WayId = 1)
SELECT * FROM(
SELECT *, Row_Number() OVER(ORDER BY [Click] DESC) AS RowNumber FROM [Topic]
) AS A
WHERE
([SpecialId] = @SpecialId OR @SpecialId IS NULL)
AND
(RowNumber BETWEEN @FirstNumber AND @LastNumber)
ELSE IF ....
SET NOCOUNT ON;
END
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货