XML Sample of Stored Procedure

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