GO
/****** Object: StoredProcedure [GetThreadPage] Script Date: 11/25/2006 23:49:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [GetThreadPage]
(
@threadid int,
@pageNum INT = 1,
@PageSize INT = 10,
@sortDir varchar(8)
)
AS
— SET the page bounds
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @TotalRecords float
SET @PageLowerBound = @PageSize * (@pageNum – 1)
SET @PageUpperBound = @PageSize – 1 + @PageLowerBound
— Create a temp table to put items in the order we want
CREATE TABLE #ItemIndex
(
IndexId int IDENTITY (0, 1) NOT NULL,
MsgId int
)
— INSERT INTO temp table
INSERT INTO #ItemIndex (MsgId)
SELECT Messages.id
FROM Messages WHERE (Messages.threadid = @threadid) ORDER BY
CASE
WHEN @sortDir = ‘desc’ Then Messages.postdate
END DESC,
CASE
WHEN @sortDir = ‘asc’ Then Messages.postdate
END ASC
IF @pageNum = 1
BEGIN
UPDATE Threads SET views = views + 1 WHERE threadid = @threadid
END
SELECT Messages.parentmsgid, Messages.threadid, Messages.body, Messages.subject, Messages.postdate, Messages.memberid, Messages.id,
MemberInfo.firstname + ‘ ‘ + MemberInfo.lastname AS displayname
FROM Messages LEFT JOIN
MemberInfo ON Messages.memberid = MemberInfo.memberid
INNER JOIN #ItemIndex i ON i.MsgId = Messages.id
WHERE (Messages.threadid = @threadid)
AND i.IndexId >= @PageLowerBound AND i.IndexId <= @PageUpperBound
ORDER BY
CASE
WHEN @sortDir = ‘desc’ Then Messages.postdate
END DESC,
CASE
WHEN @sortDir = ‘asc’ Then Messages.postdate
END ASC
SELECT @TotalRecords = COUNT(*)
FROM #ItemIndex
RETURN CEILING(@TotalRecords/@pageSize)
GO
/****** Object: StoredProcedure [GetThreads] Script Date: 07/04/2006 23:49:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [GetThreads]
@topicid int,
@sticky bit
AS
SELECT Root.id, Root.subject, Threads.locked, Root.threadid, Root.memberid, Root.startername,
MemberInfo_1.firstname + ‘ ‘ + MemberInfo_1.lastname AS displayname, Root.postdate, Threads.lastpostby, Threads.lastpostdate,
Threads.views, foo.replies
FROM Threads INNER JOIN
(SELECT Messages.parentmsgid, Messages.id, Messages.subject, Messages.threadid, Messages.postdate, Messages.memberid,
MemberInfo.firstname + ‘ ‘ + MemberInfo.lastname AS startername
FROM Messages LEFT OUTER JOIN
MemberInfo ON Messages.memberid = MemberInfo.memberid) AS Root ON Threads.threadid = Root.threadid
INNER JOIN
(SELECT COUNT(*) – 1 AS replies, threadid
FROM Messages AS Messages_1
GROUP BY threadid) AS foo ON Threads.threadid = foo.threadid
LEFT OUTER JOIN
MemberInfo AS MemberInfo_1 ON Threads.lastpostby = MemberInfo_1.memberid
WHERE (Threads.topicid = @topicid) AND Threads.sticky = @sticky AND (Root.parentmsgid = 0)
ORDER BY Threads.lastpostdate DESC
RETURN
GO
/****** Object: StoredProcedure [AddMessage] Script Date: 07/04/2006 23:49:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [AddMessage]
@parentmsgid int,
@memberid uniqueidentifier,
@body ntext,
@subject nvarchar(255),
@topicid int
AS
declare @threadid int
IF @parentmsgid = 0
BEGIN
INSERT INTO Threads (lastpostdate, lastpostby, topicid) values(GetDate(), @memberid, @topicid)
SELECT @threadid = SCOPE_IDENTITY()
END
else
BEGIN
SELECT @threadid = threadid FROM Messages WHERE id = @parentmsgid
UPDATE Threads SET lastpostdate = GetDate(), lastpostby = @memberid WHERE threadid = @threadid
END
INSERT INTO Messages
(memberid, subject, body, threadid, parentmsgid)
VALUES (@memberid,@subject,@body,@threadid,@parentmsgid)
RETURN @threadid
GO
/****** Object: StoredProcedure [DeleteMessage] Script Date: 07/04/2006 23:49:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [DeleteMessage]
@id int
AS
declare @myparent int
declare @firstchild int
declare @threadid int
declare @leftcount int
SELECT @myparent = parentmsgid FROM Messages WHERE id = @id
SELECT @threadid = threadid FROM Messages WHERE id = @id
IF @myparent <> 0
BEGIN
UPDATE Messages SET parentmsgid = @myparent WHERE parentmsgid = @id
END
else
BEGIN
SELECT @firstchild = min(id) FROM Messages WHERE parentmsgid = @id
IF @firstchild is not null
BEGIN
UPDATE Messages SET parentmsgid = 0 WHERE id = @firstchild
UPDATE Messages SET parentmsgid = @firstchild WHERE parentmsgid = @id
END
END
delete Messages WHERE id = @id
SELECT @leftcount = count (id) FROM Messages WHERE threadid = @threadid
IF @leftcount = 0
BEGIN
delete threads WHERE threadid = @threadid
END
RETURN
GO
/****** Object: StoredProcedure [GetMessage] Script Date: 07/04/2006 23:49:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [GetMessage]
@id int
AS
SELECT Messages.parentmsgid, Messages.body, Messages.subject, Messages.postdate, Messages.memberid, Messages.id,
MemberInfo.firstname + ‘ ‘ + MemberInfo.lastname AS displayname, Threads.topicid, Messages.threadid
FROM Messages LEFT OUTER JOIN
MemberInfo ON Messages.memberid = MemberInfo.memberid
LEFT OUTER JOIN
Threads ON Messages.threadid = Threads.threadid
WHERE (Messages.id = @id)
RETURN
GO
/****** Object: StoredProcedure [GetWatchedThreads] Script Date: 07/04/2006 23:49:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [GetWatchedThreads]
(
@memberid uniqueidentifier
)
AS
SELECT Root.subject, Root.threadid, MemberInfo_1.firstname + ‘ ‘ + MemberInfo_1.lastname AS displayname, Threads.lastpostdate, foo.replies
FROM Threads INNER JOIN
(SELECT Messages.subject, Messages.threadid, Messages.parentmsgid FROM Messages) AS Root ON Threads.threadid = Root.threadid
INNER JOIN
(SELECT COUNT(*) – 1 AS replies, threadid
FROM Messages AS Messages_1
GROUP BY threadid) AS foo ON Threads.threadid = foo.threadid
INNER JOIN MembersThreads mt ON Threads.threadid = mt.threadid
LEFT OUTER JOIN
MemberInfo AS MemberInfo_1 ON Threads.lastpostby = MemberInfo_1.memberid
WHERE mt.memberid = @memberid and mt.watch = 1 AND (Root.parentmsgid = 0)
ORDER BY Threads.lastpostdate DESC
RETURN
GO
/****** Object: StoredProcedure [LoadForumUser] Script Date: 07/04/2006 23:49:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [LoadForumUser]
(
@memberid uniqueidentifier
)
AS
IF NOT EXISTS (SELECT memberid from membersforums where memberid = @memberid)
BEGIN
INSERT INTO membersforums (memberid) VALUES (@memberid)
END
SELECT mi.firstname + ‘ ‘ + mi.lastname AS displayname, ISNULL(mi.address, ”) AS address, ISNULL(mi.phone, ”) AS phone, ISNULL(COUNT(me.id), 0) AS posts,
ISNULL(MAX(me.postdate), GETDATE()) AS laspostdate, mf.signature, mf.bio, mf.sortdesc, mf.msgsperpage, mf.showavatars,
mf.showsignatures, mf.sendwatchemails, mf.showemail
FROM MemberInfo AS mi
INNER JOIN MembersForums AS mf ON mi.memberid = mf.memberid
LEFT OUTER JOIN Messages AS me ON mi.memberid = me.memberid
WHERE (mi.memberid = @memberid)
GROUP BY mi.firstname, mi.lastname, mi.address, mi.phone, mf.signature, mf.bio, mf.msgsperpage, mf.sortdesc, mf.msgsperpage, mf.showavatars,
mf.showsignatures, mf.sendwatchemails, mf.showemail
RETURN
GO
/****** Object: StoredProcedure [SaveEditMessage] Script Date: 07/04/2006 23:49:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [SaveEditMessage]
@id int,
@body ntext,
@subject nvarchar(255)
AS
UPDATE Messages SET body = @body, subject = @subject WHERE id = @id
RETURN
GO
/****** Object: StoredProcedure [MarkThreadRead] Script Date: 07/04/2006 23:49:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [MarkThreadRead]
(
@threadid int,
@memberid uniqueidentifier
)
AS
SET NOCOUNT ON
IF EXISTS (SELECT lastread from membersthreads where threadid = @threadid and memberid = @memberid)
BEGIN
UPDATE membersthreads SET lastread = GETDATE() where threadid = @threadid and memberid = @memberid
RETURN
END
INSERT INTO membersthreads (memberid, threadid, lastread) VALUES (@memberid, @threadid, getdate())
RETURN
GO
/****** Object: StoredProcedure [MarkAllRead] Script Date: 07/04/2006 23:49:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [MarkAllRead]
(
@memberid uniqueidentifier
)
AS
SET NOCOUNT ON
UPDATE membersthreads SET lastread = GETDATE() where memberid = @memberid
IF EXISTS (SELECT newmsgthreshold from membersforums where memberid = @memberid)
BEGIN
UPDATE membersforums SET newmsgthreshold = GETDATE() where memberid = @memberid
RETURN
END
INSERT INTO membersforums (memberid, newmsgthreshold) VALUES (@memberid, GETDATE())
RETURN
GO
ALTER TABLE [Threads] WITH CHECK ADD CONSTRAINT [FK_Threads_Topics] FOREIGN KEY([topicid])
REFERENCES [Topics] ([topicid])
ON DELETE CASCADE
GO
ALTER TABLE [Threads] CHECK CONSTRAINT [FK_Threads_Topics]
GO
ALTER TABLE [Messages] WITH CHECK ADD CONSTRAINT [FK_Messages_Threads] FOREIGN KEY([threadid])
REFERENCES [Threads] ([threadid])
ON DELETE CASCADE
GO
ALTER TABLE [Messages] CHECK CONSTRAINT [FK_Messages_Threads]
GO
ALTER TABLE [MembersThreads] WITH CHECK ADD CONSTRAINT [FK_MembersThreads_MemberInfo] FOREIGN KEY([memberid])
REFERENCES [MemberInfo] ([memberid])
ON DELETE CASCADE
GO
ALTER TABLE [MembersThreads] CHECK CONSTRAINT [FK_MembersThreads_MemberInfo]
GO
ALTER TABLE [MembersThreads] WITH CHECK ADD CONSTRAINT [FK_MembersThreads_Threads] FOREIGN KEY([threadid])
REFERENCES [Threads] ([threadid])
ON DELETE CASCADE
GO
ALTER TABLE [MembersThreads] CHECK CONSTRAINT [FK_MembersThreads_Threads]
GO
ALTER TABLE [MembersForums] WITH CHECK ADD CONSTRAINT [FK_MembersForums_MemberInfo] FOREIGN KEY([memberid])
REFERENCES [MemberInfo] ([memberid])
ON DELETE CASCADE
GO
ALTER TABLE [MembersForums] CHECK CONSTRAINT [FK_MembersForums_MemberInfo]
Please goto ShowThread.aspx examples to continue: http://xmlpitstop.com