/*SELECT Article.Art_ID, Article.Class_ID, Article.Title, Article.ViceTitle, Article.TitleImage,
Article.TitleImage_Height, Article.TitleImage_Width, Article.Content,
Article.recommend, Article.Comment, Article.Author, Article.FromWhere,
Article.CourtName, Article.pic_suffix, Article.pic_comments, Article.ArtSource,
Article.IsAllwaysOnTop, Article.IsAllowDiscuss, Article.ArtStatus, Article.IsRecommend,
Article.Keyword, Article.ReadCount, Article.day_edit, Article.Releasedate,
Article.User_ID, Article.checkup, IZ_User.UserName, MsgClass.ClassName
FROM Article
INNER JOIN MsgClass ON Article.Class_ID = MsgClass.Class_ID
LEFT OUTER JOIN IZ_User ON Article.User_ID = IZ_User.UserID
WHERE Article.ArtStatus=1 AND Article.Class_ID=77
ORDER BY Article.IsAllwaysOnTop DESC,
Article.ReleaseDate DESC
*/
--准备存储过程的参数
declare @pagesize int
declare @pageindex int
set @pagesize=50
set @pageindex=2
--这两个排序依据的字段类型不明,按名字猜的
declare @IsAllwaysOnTop bit
declare @ReleaseDate datetime
declare @rowcount int
--以下不判断参数的正确性,比如pageindex<=1或者pageindex>pagecount
set @rowcount=(@pageindex-1)*@pagesize
set rowcount @rowcount
--得到所需要记录第一条之前的标志
SELECT @IsAllwaysOnTop=IsAllwaysOnTop,@ReleaseDate=ReleaseDate
FROM Article
INNER JOIN MsgClass ON Article.Class_ID = MsgClass.Class_ID
WHERE Article.ArtStatus=1 AND Article.Class_ID=77
ORDER BY Article.IsAllwaysOnTop DESC,
Article.ReleaseDate DESC
--然后选取@pagesize条记录
set rowcount @pagesize
SELECT Article.Art_ID, Article.Class_ID, Article.Title, Article.ViceTitle, Article.TitleImage,
Article.TitleImage_Height, Article.TitleImage_Width, Article.Content,
Article.recommend, Article.Comment, Article.Author, Article.FromWhere,
Article.CourtName, Article.pic_suffix, Article.pic_comments, Article.ArtSource,
Article.IsAllwaysOnTop, Article.IsAllowDiscuss, Article.ArtStatus, Article.IsRecommend,
Article.Keyword, Article.ReadCount, Article.day_edit, Article.Releasedate,
Article.User_ID, Article.checkup, IZ_User.UserName, MsgClass.ClassName
FROM Article
INNER JOIN MsgClass ON Article.Class_ID = MsgClass.Class_ID
LEFT OUTER JOIN IZ_User ON Article.User_ID = IZ_User.UserID
WHERE Article.ArtStatus=1 AND Article.Class_ID=77
--加入条件,要比刚才的记录排在更后
AND Article.IsAllwaysOnTop>@IsAllwaysOnTop and Article.ReleaseDate>@ReleaseDate
ORDER BY Article.IsAllwaysOnTop DESC,
Article.ReleaseDate DESC
存储过程如下:
分页的存储过程:
--用法:
-- EXEC GetRecordPage 'SELECT * FROM TAB WHERE ...... ',10,100
CREATE PROC GetRecordPage
@strSQL varchar(4000), --查询SQL语句字符串
@CurPageNum int=1, --要显示的第N页
@PageSize int=50 --每页行数
AS
SET NOCOUNT ON
DECLARE @P1 int, --P1是游标的id
@RowCount int,
@PageCount int,
@CurPage int
EXEC sp_cursoropen @P1 OUTPUT,@strSQL,@scrollopt=1,@ccopt=1,@RowCount=@RowCount OUTPUT
SET @PageCount = ceiling(1.0*@RowCount/@PageSize)
IF @CurPageNum>@PageCount
SET @CurPage = @PageCount
ELSE IF @CurPageNum<=0
SET @CurPage = 1
ELSE
SET @CurPage = @CurPageNum
SELECT @RowCount AS RsCount,@PageCount AS PageCount,@CurPage AS CurPage
SET @CurPageNum=(@CurPageNum-1)*@PageSize+1
EXEC sp_cursorfetch @P1,16,@CurPageNum,@PageSize
EXEC sp_cursorclose @P1
--SET NOCOUNT OFF
GO
这个是执行的存储过程:
exec GetRecordPage 'SELECT Article.Art_ID, Article.Class_ID, Article.Title, Article.ViceTitle, Article.TitleImage,
Article.TitleImage_Height, Article.TitleImage_Width, Article.Content,
Article.recommend, Article.Comment, Article.Author, Article.FromWhere,
Article.CourtName, Article.pic_suffix, Article.pic_comments, Article.ArtSource,
Article.IsAllwaysOnTop, Article.IsAllowDiscuss, Article.ArtStatus, Article.IsRecommend,
Article.Keyword, Article.ReadCount, Article.day_edit, Article.Releasedate,
Article.User_ID, Article.checkup, IZ_User.UserName, MsgClass.ClassName
FROM Article INNER JOIN
MsgClass ON Article.Class_ID = MsgClass.Class_ID LEFT OUTER JOIN
IZ_User ON Article.User_ID = IZ_User.UserID
WHERE Article.ArtStatus=1 AND Article.Class_ID=77 ORDER BY Article.IsAllwaysOnTop DESC, Article.ReleaseDate DESC'
,1,55
然后我希望在数据集中看到数据,可是数据为空,在查询分析器上执行的结果为三个表,只有第三个表才是我想要的,怎么做呢?