Community-Credit.com | NonProfitWays.com | SOAPitstop.com   Skin:   
      User: Not logged in 
Home
Newsletter Signup
XSLT Library
Latest XML Blogs
Featured Examples
Presentations
Featured Articles
Book Chapters
Training Courses
Events
NewsGroups
 
Discussions
Examples
Tutorials
Tools
Articles
Resources
Websites
 
Sign In
My Profile
My Articles
My Examples
My Favorites
My Resources
Add a Resource
Logout
 
About Me
My Blog
HeadGeek Articles
Talking Portfolio
Resume
Pictures
World Trip Pics

Stored Procedures for Forums
Author: Malinda Ng
Published: 11/28/2006 9:51 PM
Category: ADO.NET, Security, Database, SQL
Summary: These are sample of stored procedure that I created my forum:Discussion board.

Check it out: http://m.aatechsources.com

 

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


 

 
Fans of "The Office"
Dwight Bobbleheads are here!
  “It's me! I'm the bobblehead! Yes!”



Advertise on XMLPitstop

Advertise on XMLPitstop


EggHead Cafe
Web Servicee development
DotNetSlackers
Buy text ads here!
desktop online backup
Color Laser Printer
Dior sunglasses
Skype vs. sipcall
VoIP Internettelefonie
Buy text ads here!

Interested in Text ads?
2,222 Total Members
35 members(last 30 days)
7 members(last 7 days)
1 members(today)

1,609 Total Discussions
12 Posts(last 30 days)
0 Posts(last 7 days)
0 Posts(today)

17,169 Total Blog Posts
1,748 Blogs(last 30 days)
251 Blogs(last 7 days)
0 Blogs(today)

8,699 Newsgroup Posts
0 Posts(last 30 days)
0 Posts(last 7 days)
0 Posts(today)

13,786 Total Resources
5 Resources(last 30 days)
1 Resources(last 7 days)
0 Resources(today)


 

David Silverlight's XMLPitstop.com| 2801 Florida Ave #225|Miami, FL 33133|Ph:305-447-1139