quote:

---

Originally posted by GilaMonster

Sorry, could you explain clearer please?

What's the procedure doing that it shouldn't be, or what is it not doing that it should be?

--
Gail Shaw
SQL Server MVP

---

quote:

---

Originally posted by GilaMonster

Then write a new procedure that has virtually the same query, takes a parameter for the categoryID and filters on that categoryID in the where clause of the query.

CREATE PROCEDURE dbo.tbh_Forums_GetLast4PostsForCategory @CategoryID int
AS
... rest of query here ...
WHERE tbh_Posts.ParentPostID = 0 AND tbh_Forums.CategoryID = @CategoryID
ORDER BY ...

--
Gail Shaw
SQL Server MVP

---

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by GilaMonster

Then write a new procedure that has virtually the same query, takes a parameter for the categoryID and filters on that categoryID in the where clause of the query.

CREATE PROCEDURE dbo.tbh_Forums_GetLast4PostsForCategory @CategoryID int
AS
... rest of query here ...
WHERE tbh_Posts.ParentPostID = 0 AND tbh_Forums.CategoryID = @CategoryID
ORDER BY ...

--
Gail Shaw
SQL Server MVP

---

No need of new procedure. you could just create a optional parameter CategoryID which when passed will return info for that category else will give entire info for all categories. somethink like

CREATE PROCEDURE dbo.tbh_Forums_GetLast4PostsForCategory @CategoryID int=NULL
AS
... rest of query here ...
WHERE tbh_Posts.ParentPostID = 0
AND (tbh_Forums.CategoryID = @CategoryID OR @CategoryID IS NULL)
ORDER BY ...

---

quote:

---

Originally posted by krainov

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by GilaMonster

Then write a new procedure that has virtually the same query, takes a parameter for the categoryID and filters on that categoryID in the where clause of the query.

CREATE PROCEDURE dbo.tbh_Forums_GetLast4PostsForCategory @CategoryID int
AS
... rest of query here ...
WHERE tbh_Posts.ParentPostID = 0 AND tbh_Forums.CategoryID = @CategoryID
ORDER BY ...

--
Gail Shaw
SQL Server MVP

---

No need of new procedure. you could just create a optional parameter CategoryID which when passed will return info for that category else will give entire info for all categories. somethink like

CREATE PROCEDURE dbo.tbh_Forums_GetLast4PostsForCategory @CategoryID int=NULL
AS
... rest of query here ...
WHERE tbh_Posts.ParentPostID = 0
AND (tbh_Forums.CategoryID = @CategoryID OR @CategoryID IS NULL)
ORDER BY ...

---

Thats right! But the categoryID is in different table! I have no CategoryID in Posts table! The forums table has the CategoryID key

---

ALTER PROCEDURE dbo.tbh_Forums_GetLast4Posts
(
	@CategoryID INT = NULL
)
AS

SET NOCOUNT ON

SELECT TOP 4	p.PostID, 
		p.AddedDate, 
		p.AddedBy, 
		p.AddedByIP, 
		p.ForumID, 
		p.ParentPostID,
		p.Title,
		p.Body, 
		p.Approved, 
		p.Closed, 
		p.ViewCount, 
		p.ReplyCount, 
		p.LastPostDate, 
		p.LastPostBy,
		d.Title AS ForumTitle,
		d.CategoryID AS CategoryID, 
		p.Priority,
		fc.Title AS CategoryTitle
FROM		tbh_Posts AS p
INNER JOIN	tbh_Forums AS f ON f.ForumID = p.ForumID
LEFT JOIN	dbo.tbh_Forums_Categories AS fc ON fc.CategoryID = f.CategoryID
WHERE		p.ParentPostID = 0
		AND (f.CategoryID = @CategoryID OR @CategoryID IS NULL)
ORDER BY	p.AddedDate DESC