==>Table Structure
CREATE TABLE [dbo].[Tbl_ForumCategory](
[CategoryID] [bigint] IDENTITY(1,1) NOT NULL,
[CategoryName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[IsPublish] [bit] NULL CONSTRAINT [DF_Tbl_ForumCategory_IsPublish] DEFAULT (0),
[CreationDate] [datetime] NULL CONSTRAINT [DF_Tbl_ForumCategory_CreationDate] DEFAULT (getdate()),
[ModificationDate] [datetime] NULL CONSTRAINT [DF_Tbl_ForumCategory_ModificationDate] DEFAULT (getdate()),
CONSTRAINT [PK4] PRIMARY KEY NONCLUSTERED
(
[CategoryID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Tbl_ForumTopic](
[TopicID] [bigint] IDENTITY(1,1) NOT NULL,
[FK_CategoryID] [bigint] NULL CONSTRAINT [DF_Tbl_ForumTopic_FK_CategoryID] DEFAULT (0),
[TopicName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[TopicView] [int] NULL CONSTRAINT [DF_Tbl_ForumTopic_TopicView] DEFAULT (0),
[IsPublish] [bit] NULL CONSTRAINT [DF_Tbl_ForumTopic_IsPublish] DEFAULT (1),
[CreationDate] [datetime] NULL CONSTRAINT [DF_Tbl_ForumTopic_CreationDate] DEFAULT (getdate()),
[ModificationDate] [datetime] NULL CONSTRAINT [DF_Tbl_ForumTopic_ModificationDate] DEFAULT (getdate()),
[CreatedBy] [bigint] NULL CONSTRAINT [DF_Tbl_ForumTopic_CreatedBy] DEFAULT (1),
CONSTRAINT [PK2] PRIMARY KEY NONCLUSTERED
(
[TopicID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Tbl_ForumPost](
[PostID] [bigint] IDENTITY(1,1) NOT NULL,
[FK_CategoryID] [bigint] NULL,
[FK_TopicID] [bigint] NOT NULL,
[FK_QuestionID] [bigint] NULL CONSTRAINT [DF_Tbl_ForumPost_FK_QuestionID] DEFAULT (0),
[ParentPostID] [bigint] NOT NULL,
[PostTitle] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PostDescription] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[TypeOfPost] [bit] NULL,
[UserType] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IsPublish] [bit] NULL,
[CreationDate] [datetime] NULL CONSTRAINT [DF_Tbl_ForumPost_CreationDate] DEFAULT (getdate()),
[Modificationdate] [datetime] NULL CONSTRAINT [DF_Tbl_ForumPost_Modificationdate] DEFAULT (getdate()),
[CreatedBy] [bigint] NOT NULL,
[AbuseComments] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IsAbuseReport] [int] NULL,
[AbuseUser] [int] NULL,
[IsBlockedUser] [bit] NULL,
CONSTRAINT [PK3] PRIMARY KEY NONCLUSTERED
(
[PostID] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
==>Procedure
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
--set QUOTED_IDENTIFIER OFF
--Proc_ForumCrumbTrail 1
ALTER Proc [dbo].[Proc_ForumCrumbTrail]
@PostID int
AS
BEGIN
CREATE TABLE #TempPost (
POSTID BIGINT IDENTITY(1,1) NOT NULL,
PostString varchar(8000))
CREATE TABLE #TempPostNew (
POSTID BIGINT ,
PostString varchar(8000))
Declare @HomePageLink varchar(100),@HomePage varchar(100),@QuestionPage varchar(100),@TopicPage varchar(100) ,@PostPage varchar(100)
Declare @strForumCrumbTail varchar(8000)
Declare @strForumCrumbTailPost varchar(8000)
Declare @strPostString varchar(8000)
Declare @CategoryID varchar(10),@TopicID varchar(10),@ParentPostID varchar(100),@TempPostID varchar(10)
Declare @CategoryName varchar(20),@TopicName varchar(20),@PostName varchar(20)
SET @strForumCrumbTailPost = ''
SET @strForumCrumbTail = ''
set @CategoryID=''
set @TopicID=''
set @QuestionPage='ForumListQuestion.aspx'
set @TopicPage ='ForumTopicList.aspx'
set @PostPage ='ForumListAllPostByPostID.aspx'
set @HomePage ='ForumCategoryList.aspx'
set @HomePageLink = '
Home'
select @CategoryID=FK_CategoryID,@TopicID=FK_TopicID,@ParentPostID=ParentPostID
from tbl_ForumPost where PostID=@PostID
if(@CategoryID = '')
BEGIN
select @CategoryID=FK_CategoryID,@TopicID=TopicID
from tbl_ForumTopic where TopicID=@PostID
set @ParentPostID =''
END
if(@CategoryID !='')
BEGIN
select @CategoryName=CategoryName from tbl_ForumCategory where CategoryID=@CategoryID
set @strForumCrumbTail = '
'+@CategoryName + ''
END
if(@TopicID !='')
BEGIN
select @TopicName=TopicName from tbl_ForumTopic where TopicID=@TopicID
set @strForumCrumbTail = @strForumCrumbTail+' >> '+'
'+@TopicName + ''
END
if(@PostID !='')
BEGIN
if(@ParentPostID > 0)
BEGIN
While(@ParentPostID > 0)
Begin
select @TempPostID = PostID,@ParentPostID = ParentPostID,@PostName=PostTitle from tbl_ForumPost where PostID= @ParentPostID
set @strPostString = '
'+@PostName + '' + ' >> '
INSERT INTO #TempPost(PostString)values(@strPostString)
End
END
END
INSERT INTO #TempPostNew(POSTID,PostString)
select POSTID,PostString from #TempPost ORDER BY 1 desc
SELECT @strForumCrumbTailPost = @strForumCrumbTailPost + PostString FROM #TempPostNew
if(@strForumCrumbTailPost <>'')
BEGIN
set @strForumCrumbTail = @HomePageLink+' >> '+ @strForumCrumbTail + ' >> '+ @strForumCrumbTailPost
SELECT substring(@strForumCrumbTail,0,len(@strForumCrumbTail)-2)
END
ELSE
BEGIN
set @strForumCrumbTail = @HomePageLink+' >> '+ @strForumCrumbTail
SELECT @strForumCrumbTail
END
END
