iram friendly speaking, i didn't ever create such procedure not even heard about it.

I don't have SQL server installed on my home machine.

i'll check it tomorrow and conclude my point.

but i read about recursive queries (CTE).

Meanwhile if could specify your problem in more detail then it might be possible that there are some best way around /alternatives for your problem.

---------------------------------------------------------CTE Reference------------------------------
http://msdn.microsoft.com/en-us/library/ms190766%28v=SQL.90%29.aspx

http://msdn.microsoft.com/en-us/library/ms190766%28v=SQL.90%29.aspx

http://stackoverflow.com/questions/5280585/sql-server-2008-recursive-stored-proc

http://msdn.microsoft.com/en-us/library/ms190778%28v=sql.105%29.aspx

Yes Iram we could use recursive stored procedures but
the Maximum stored procedure , function,trigger and view nesting level is 32.

The best approach is through CTE.

============First=========================Recursive Stored Procedure========================

CREATE PROCEDURE [dbo].[Factorial_ap]
(
@Number Integer,
@RetVal Integer OUTPUT
)
AS
DECLARE @In Integer
DECLARE @Out Integer
IF @Number != 1
BEGIN
SELECT @In = @Number – 1
EXEC Factorial_ap @In, @Out OUTPUT
SELECT @RetVal = @Number * @Out
print @RetVal
END
ELSE
BEGIN
SELECT @RetVal = 1
print @RetVal
END
RETURN
GO

-- Uncomment when you execute the above funtion 's script and execute below lines.
-- Declare @returnVal integer
-- Exec [dbo].[Factorial_ap] 5,@returnVal


===========Second========================Recursive Function==================================

Create function Fib(@n1 int, @n2 int, @count int
)

returns @table (i)
As

Begin
Declare @n3 int
set @n3 = @n2 + @n1
set @count = @count -1

If @count <> 0
Insert into @table
Select @n3
union select * from dbo.Fib(@n2, @n3,@count)
return
End


-- Uncomment when you execute the above funtion 's script and execute below line.
--Select * from dbo.Fib(0,1,20)



=============================(CTE)=====================Third and Most Efficient Solution========================

WITH Fibonacci(n, f, f1)
AS (
-- This is the anchor part
-- Initialize level to 1 and set the first two values as per definition
SELECT CAST(1 AS BIGINT),
CAST(0 AS BIGINT),
CAST(1 AS BIGINT)

UNION ALL

-- This is the recursive part
-- Calculate the next Fibonacci value using the previous two values
-- Shift column (place) for the sum in order to accomodate the previous
-- value too because next iteration need them both
SELECT n + 1,
f + f1,
f
FROM Fibonacci
-- Stop at iteration 93 because we than have reached maximum limit
-- for BIGINT in Microsoft SQL Server
WHERE n < 93
)
-- Now the easy presentation part
SELECT n,
f AS Number
FROM Fibonacci

Most efficient way is CTE. we shouldn't use recursive stored procedures nor functions.

see more details related to CTE:

http://msdn.microsoft.com/en-us/library/ms186243%28v=sql.105%29.aspx