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
