ALTER PROCEDURE dbo.forum_GetPostsToCategory
(
@CategoryId int,
@PageSize int = 30,
@UserName varchar(256),
@CurrentPage int = 1
)
AS
BEGIN
SELECT p.UserName, p.PostId, p.Header, p.Date, p.Locked, p.Sticky, p.Anonymous, p.Visit
FROM forum_Post AS p
left outer join forum_Post AS op
ON op.ParentPostId = p.PostId
WHERE (p.CategoryId = @CategoryId AND p.ParentPostId = 0) AND p.Deleted = 0
GROUP BY p.PostId, op.Date, p.ParentPostId, p.CategoryId, p.UserId, p.Header, p.Date, p.Message, p.Locked, p.Sticky, p.Deleted, p.Anonymous, p.Visit, p.UserName
ORDER BY p.Sticky DESC, LastPostDate DESC
Nu vill jag räkna ut mha av @CurrentPage samt @PageSize vilka poster som ska hämtas. I MySQL går det ju att anv. LIMIT men hur gör man i MSSQL? Om man tex enbart ska visa posterna 31-60?
ALTER PROCEDURE dbo.forum_GetPostsToCategory
(
@CategoryId int,
@PageSize int = 30,
@UserName varchar(256),
@CurrentPage int = 1
)
AS
BEGIN
WITH forumPosts AS
(
SELECT p.UserName, p.PostId, p.Header, p.Date, p.Locked, p.Sticky, p.Anonymous, p.Visit, p.LastPostDate, ROW_NUMBER() OVER (p.Sticky DESC, LastPostDate DESC) AS RowNumber
FROM forum_Post AS p
left outer join forum_Post AS op
ON op.ParentPostId = p.PostId
WHERE (p.CategoryId = @CategoryId AND p.ParentPostId = 0) AND p.Deleted = 0
GROUP BY p.PostId, op.Date, p.ParentPostId, p.CategoryId, p.UserId, p.Header, p.Date, p.Message, p.Locked, p.Sticky, p.Deleted, p.Anonymous, p.Visit, p.UserName, p.LastPostDate
)
DECLARE @startRow int
DECLARE @endRow int
SELECT @startRow = (@CurrentPage - 1) * @PageSize + 1
SELECT @endRow = @startRow + @pageSize - 1
SELECT * FROM forumPosts f WHERE f.RowNumber BETWEEN @startRow AND @endRow ORDER BY f.Sticky DESC, f.LastPostDate DESC
Tack, får dock felmeddelandet "Incorrect syntax near '.'." Inte mycket den talade om :(
Ang. GROUP BY så vet jag inte om det behövs, la till de allt eftersom felmeddelanden kom ang det.
Så frågan är då vad syntax felet är?
CREATE PROCEDURE dbo.forum_GetPostsToCategory
(
@CategoryId int,
@PageSize int = 30,
@UserName varchar(256),
@CurrentPage int = 1
)
AS
BEGIN
WITH forumPosts AS
(
SELECT p.UserName, p.PostId, p.Header, p.Date, p.Locked, p.Sticky, p.Anonymous, p.Visit, p.LastPostDate, ROW_NUMBER() OVER (ORDER BY p.Sticky DESC, p.LastPostDate DESC) AS RowNumber
FROM forum_Post AS p
left outer join forum_Post AS op
ON op.ParentPostId = p.PostId
WHERE (p.CategoryId = @CategoryId AND p.ParentPostId = 0) AND p.Deleted = 0
GROUP BY p.PostId, op.Date, p.ParentPostId, p.CategoryId, p.UserId, p.Header, p.Date, p.Message, p.Locked, p.Sticky, p.Deleted, p.Anonymous, p.Visit, p.UserName, p.LastPostDate
)
SELECT * FROM forumPosts f WHERE f.RowNumber BETWEEN (@CurrentPage - 1) * @PageSize + 1 AND @PageSize * @CurrentPage ORDER BY f.Sticky DESC, f.LastPostDate DESC
