---
title: "Problem med lagrad procedur"
type: "forum-thread"
url: "https://www.webforum.nu/amne/databaser-sql/179517-problem-med-lagrad-procedur"
topic: "Databaser & SQL"
topic_url: "https://www.webforum.nu/amne/databaser-sql"
author: "Vinnaren"
published: "2009-07-25T15:06:55.000Z"
updated: "2009-07-26T19:48:41.000Z"
replies: 5
views: 448
page: 1
pages: 1
language: "sv-SE"
site: "webForum — webforum.nu"
rights: "Upphovsrätten till varje inlägg tillhör dess författare."
attribution: "Citera som: webForum, https://www.webforum.nu/amne/databaser-sql/179517-problem-med-lagrad-procedur"
---

# Problem med lagrad procedur

## #1 — Vinnaren, 2009-07-25T15:06Z

Hej,

Har förljande:

```
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?

Tacksam för all hjälp :stud

Permalänk: https://www.webforum.nu/p/179517

## #2 — doggelito, 2009-07-25T15:59Z

<http://blogs.x2line.com/al/archive/2005/11/18/1323.aspx>
<http://blogs.x2line.com/al/archive/2004/05/29/325.aspx>
<http://www.google.se/search?hl=sv&num=100&q=ms+sql+paging&btnG=S%C3%B6k&meta=>

Permalänk: https://www.webforum.nu/p/2193942

## #3 — Peter S, 2009-07-25T16:00Z

```
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
```

Notera att jag lagt till p.LastPostDate.

Varför har du med GROUP BY, är det nödvändigt?

Permalänk: https://www.webforum.nu/p/2193943

## #4 — Vinnaren, 2009-07-25T16:39Z

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?

Permalänk: https://www.webforum.nu/p/2193946

## #5 — Peter S, 2009-07-25T18:06Z

Oops, så här skall det vara:

```
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
```

Permalänk: https://www.webforum.nu/p/2193957

## #6 — Vinnaren, 2009-07-26T19:48Z

tack, verkar funka kanon :)

Permalänk: https://www.webforum.nu/p/2194046

---

Tråden på webben: https://www.webforum.nu/amne/databaser-sql/179517-problem-med-lagrad-procedur