I want to write a scalar function in T-SQL i.e factorial which nested on 33 levels so how can write it?
My mean to say I want use recursion in scalar function in T-SQL.
Loading
Know the answer? Post it — somebody with the same question will find it here.
Sign in to answer this question
It is the same account you read, post and publish with — and you will come straight back to this page.
Santhosh Kumar JayaramanPosted Oct 12, 2012, 8:08 AM
User-defined functions can be nested up to 32 levels. Exceeding the maximum levels of nesting causes the whole calling function chain to fail. Any reference to managed code from a Transact-SQL user-defined function counts as one level against the 32-level nesting limit.
http://msdn.microsoft.com/en-us/library/ms191320.aspx
Sandeep Singh ShekhawatPosted Oct 12, 2012, 7:23 AM
But I am not looking this type answer. Can you please explain what does happen when we will go in level 33 in case of function not for trigger?
Santhosh Kumar JayaramanPosted Oct 12, 2012, 7:15 AM
Both DML and DDL triggers are nested when a trigger performs an action that initiates another trigger. These actions can initiate other triggers, and so on. DML and DDL triggers can be nested up to 32 levels.
From msdn
http://msdn.microsoft.com/en-us/library/ms190739.aspx
Sandeep Singh ShekhawatPosted Oct 12, 2012, 7:12 AM
I went through your provide code and getting this error. So I have already mention that nested level is 33.
Santhosh Kumar JayaramanPosted Oct 12, 2012, 6:54 AM
( @iNumber bigint )
RETURNS INT
AS
BEGIN
DECLARE @i bigint
IF @iNumber <= 1
SET @i = 1
ELSE
SET @i = @iNumber * dbo.Factorial( @iNumber - 1 )
RETURN (@i)
END
--to test
declare @int bigint
exec @int= dbo.Factorial 12
select @int