Recursion - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Recursion

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-10-18 : 07:49:32

sonali writes "Hi
for shopping cart app i'm creating Review.aspx page,where the user can post a new review or he can reply to an existing review.Here in database i'm having one table for review.if the person post a new post then Parent reviewid=0,and if he reply an existing one then Parent reviewid=reviewid.i'm showing the Reviews in a datalisti've the following store procedure,But as i'm taking Parent reviewid=0 for first cursor,in datalist it is showin only the post which is posted for the first time and it' reply.But a person if replies to the Next post(parentreviewid=1 or 2 )means reply to a person whose parent reviewid not equal to 0 ,then it is not showing the data.The problem will go on if next another person reply to any existing reply.Please kindly guide me.Is there some other way,Can i use Recursion for it.How recursion can be used for it.
CREATE PROCEDURE USP_SDB_GetReview (@iProductid as int)
as
declare @vcSubject varchar(50)
declare @iReviewID Int
declare @iParentReviewID int
declare @vcComment varchar(500)
declare @islno int
declare @vcNickName varchar(50)
begin
set @islno=0

Create table #Review(ReviewID int,Subject varchar(50),Comment varchar(500),NickName varchar(50),ParentReviewID int)

Declare Review cursor for select ReviewID,Subject,Comment,NickName,ParentReviewID from SDB_Reviews where (ProductID=@iProductid) and (ParentReviewID=0)
open Review
fetch Next from Review into @iReviewID,@vcSubject,@vcComment,@vcNickName,@iParentReviewID while @@FETCH_STATUS=0
Begin

insert into #Review(ReviewID,Subject,Comment,NickName,ParentReviewID) values(@iReviewID,@vcSubject,@vcComment,@vcNickName,@iParentReviewID)
Declare Sub cursor for select ReviewID,Subject,Comment,NickName,ParentReviewID from SDB_Reviews where (ProductID=@iProductid) and (ParentReviewID=@iReviewID)
open Sub
fetch Next from Sub into @iReviewID,@vcSubject,@vcComment,@vcNickName,@iParentReviewID while @@FETCH_STATUS=0
Begin

insert into #Review(ReviewID,Subject,Comment,NickName,ParentReviewID) values(@iReviewID,@vcSubject,@vcComment,@vcNickName,@iParentREviewID)
fetch Next from Sub into @iReviewID,@vcSubject,@vcComment,@vcNickName,@iParentReviewID
end
close Sub
Deallocate Sub
fetch Next from Review into @iReviewID,@vcSubject,@vcComment,@vcNickName,@iParentReviewID
end
close Review
Deallocate review
select * from #Review
Drop table #Review
end
GO
Please help me"

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-10-18 : 19:59:57

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56579

Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources