How can write scalar function in T-SQL ?

Question

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.

Santhosh Kumar Jayaraman · Answer

Not only for triggers stored proc user defined functions all can be nested till 32 levels and after that it will fail. 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 Shekhawat · Answer

Thanks Santosh to response back. 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 Jayaraman · Answer

Maximum nested level allowed is 32. 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 Shekhawat · Answer

Thanks Santosh I went through your provide code and getting this error. So I have already mention that nested level is 33.

Santhosh Kumar Jayaraman · Answer

create FUNCTION dbo.Factorial ( @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