Comment Hierarchy Comments Section Stored Procedure in SQL Server

Posted By : Kumar dec

Tuesday, 26 July 2016

In this article we create stored procedure for Hierarchy Comment Section which i created Database Table in my previous article : Comment Hierarchy Comments Section Database in SQL Server.

StoredProcedure : spCheckUserCredentials

This spCheckUserCredentials is used to check whether username and password already exist or not.

create procedure spCheckUserCredentials(
@Username varchar(25))
As
 Begin
  Select * from UserTable where Username=@Username
 End

StoredProcedure : spCommentInsert

This spCommentInsert is used to Insert new Parent Comment in database table.

create procedure spCommentInsert(
@Username varchar(25),
@CommentMessage varchar(300))
As
 Begin
  Insert into ParentCommentTable(Username, CommentMessage, CommentDate)
         values(@Username, @CommentMessage, GETDATE())
 End

StoredProcedure : spCommentUpdate

create procedure spCommentUpdate(
@CommentID int,
@Username varchar(25),
@CommentMessage varchar(300))
As
 Begin
  Update ParentCommentTable set CommentMessage=@CommentMessage 
                            where CommentID=@CommentID and Username=@Username
 End

StoredProcedure : spCommentReplyInsert

This spCommentReplyInsert is used to Insert reply of the parent commment.

create procedure spCommentReplyInsert(
@Username varchar(25),
@CommentMessage varchar(300),
@ParentCommentID int)
As
 Begin
  Insert into ChildCommentTable(Username, CommentMessage, CommentDate, ParentCommentID)
         values(@Username,@CommentMessage, GETDATE(),@ParentCommentID)
 End

StoredProcedure : spGetParentComment

This storedprocedure is used to get all comments which are stored in ParentCommentTable.

create procedure spGetParentComment
As
 Begin
  Select * from ParentCommentTable
 End

StoredProcedure : spGetParentCommentByUsername

This storedprocedure is used to get particular user comments stored in ParentCommentTable in Database.

create procedure spGetParentCommentUsername(
@Username varchar(25))
As
 Begin
  Select * from ParentCommentTable where Username=@Username
 End

StoredProcedure : spGetChildCommentByParentCommentID

This storedprocedure is used to get all Comments whose parent Comment ID is matching.

create procedure spGetParentCommentByParentCommentID(
@ParentCommentID int)
As
 Begin
  Select * from ChildCommentTable where ParentCommentID=@ParentCommentID
 End

1 2 3 4 5 6

Tag : ASP.NET, ASPNET Project, comments, Project, sql server