September 16, 2003 at 8:55 am
hello:
I want to know whether SQLserver 2000 stoed procedure can recursive self??
September 16, 2003 at 9:07 am
hi,
seems that there's no techinal reason why not, but you need to be aware of @@nestlevel. if it reaches 32 the transaction is terminated. (see @@nestlevel in BOL)
create proc pgr_recursive
as
select @@NESTLEVEL AS 'Nest Level'
if @@nestlevel < 32
begin
exec pgr_recursive
end
else
begin
print "recursive limit reached"
end
go
Paul
September 16, 2003 at 10:49 pm
Example:
Create Procedure Fact(@Val as Int,@Answ as Int Output) as
If @Val<2
Begin
Set @Answ=1
End
Else
Begin
Set @Val=@Val-1
Exec Fact @Val,@Answ Output
Set @Answ=@Answ*(@Val+1)
End
Return
Go
Declare @Answ Int
--
Exec Fact 4,@Answ Output
Select @Answ
-- Break it
Exec Fact 100,@Answ Output
Select @Answ
Go
Drop Procedure Fact
Go
September 17, 2003 at 4:48 am
hi!
it is possible (limitations already posted), but you should just avoid it due to performance reasons! you see, T-SQL is a very limited language and just not built for complex programming tasks like recursons. we tend to either solve such cases outside SQL server (middleware) or directly integrate complex solutions into SQL server by using extended stored procedures.
hopefully this will turn better once c# is integrated into the server (like other servers "speak" java) but for that we'll all have to wait for yukon.
best regards,
chris.
September 17, 2003 at 7:01 am
THANKS ALL
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply