November 5, 2007 at 12:16 am
How to calculate a factorial of a number without using a stored procedure?
Thanks in advance.....
November 5, 2007 at 12:59 am
Is function OK, then?
N 56°04'39.16"
E 12°55'05.25"
November 5, 2007 at 1:01 am
I don't get why recursive calls are so popular for factorial calculations...
CREATE FUNCTION dbo.fnFactorial
(
@n BIGINT
)
RETURNS BIGINT
AS
BEGIN
DECLARE @i BIGINT
IF @n >= 0 AND @n <= 20
SELECT @i = 1
WHILE @n > 1
SELECT @i = @i * @n,
@n = @n - 1
RETURN @i
END
N 56°04'39.16"
E 12°55'05.25"
November 5, 2007 at 1:19 am
Thanks lot.... SQL script is possible?
any ideas. Thanks..
November 6, 2007 at 4:10 pm
Factorials are a perfect problem to use when teaching the concept of recursion. The solution is very simple and the definition is already stated in recursive form! ITRW, it is too simple -- so looping is used for better performance.
If I understand the question, you want to use recursion without using a stored procedure or function -- just using an anonymous block of SQL script. Actually, it doesn't matter if you're asking about SQL or any other computer language, the answer is no. In order to use recursion, a block of code must reference itself. An anonymous block of code cannot be referenced. And if you try to use labels and GOTOs then you are just impersonating a loop.:P
However, if you already have a recursive algorithm for solving your problem but you don't want to use recursion then, yes, you can convert any recursive algorithm into an ordinary looping structure.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
November 6, 2007 at 6:56 pm
There is a solution without loops - single "select" (according to what "SQL" stands for).
It involves very famous table Tally (regards to you know who ;))
[Code]
DECLARE @I BIGINT
select @I = ISNULL(@I, 1) * N
FROM dbo.Tally
WHERE N > 0 AND N < = 5
SELECT @I
[/Code]
_____________
Code for TallyGenerator
November 6, 2007 at 8:37 pm
Heh... this reminds me of doing volumetric rotations in Calc II... instructor was busting our chops about how he could do immensely accurate calcs to calculate the amount of material required to build some extremely complicated tri-axial objects in only about an hour using only a hand held calculator. He got pretty ticked when I told him I could do it in less than 30 seconds by just carefully submerging the object in a full bucket of water and measuring the water that spilled out of the bucket. 😛
Same holds true here... you're only going to be able to get 20 factorial products into the likes of a BIGINT... why not calculate them just once and put them into a table? Then, the query would be...
SELECT FactorialProduct
FROM Factorial
WHERE Factor = somenumber
😉
--Jeff Moden
November 6, 2007 at 8:59 pm
But if capacity is more important for you than precision to the last digit then you probably will go with FLOAT result.
For N = 115 it returns 2.9250936934930141E+188 and still takes no time.
Limitation moves to N=170 with output = 7.257415615307994E+306
_____________
Code for TallyGenerator
November 6, 2007 at 9:10 pm
True enough... and nicely done. I was being a bit sarcastic because the OP didn't want to use a stored procedure... I went a bit overboard with a way to do it in a single query with no declarations 😉
Sometimes the requirements these poor folks get just amaze me... :hehe:
--Jeff Moden
November 6, 2007 at 9:40 pm
Thanks lot for all people....
But i got some idea from internet......
-- Written by: Greg Larsen
DECLARE @OUT BIGINT
DECLARE @CMDX NVARCHAR(4000)
DECLARE @Y INT
-- set @Y to the number for which you want to find the factorial
-- only works from 0 to 15 due to nesting level limit
SET @Y=15
-- declare the recursive command
SET @CMDX = 'DECLARE @CMD NVARCHAR(4000) ' + CHAR(13) +
'DECLARE @X BIGINT' + CHAR(13) +
'SET @X = @Y' + CHAR(13) +
'IF @Y = 0' + CHAR(13) +
' SELECT @OUT = 1' + CHAR(13) +
'ELSE' + CHAR(13) +
'BEGIN' + CHAR(13) +
-- execute another time
' set @Y=@Y-1 ' + CHAR(13) +
' SET @CMD = @CMDX' + CHAR(13) +
' EXEC SP_EXECUTESQL @CMD,N''@Y INT,@CMDX NVARCHAR(1000),
@OUT BIGINT OUT'',@Y=@Y,@cmdx=@cmdx,@OUT=@OUT OUT' + CHAR(13) +
' SELECT @OUT = @OUT * @X' + CHAR(13) +
'END'
-- Initialize the factorial value
SET @OUT = 1
-- execute the recursive call
EXEC SP_EXECUTESQL @CMDX,N'@Y INT,@CMDX NVARCHAR(1000),
@OUT BIGINT OUT',@Y=@Y,@CMDX=@CMDX,@OUT=@OUT OUT
-- Print out the factorial answer
PRINT 'The Factorial of ' + RTRIM(CAST(@Y AS CHAR)) +
' = ' + RTRIM(CAST(@OUT AS CHAR))
November 6, 2007 at 10:10 pm
Jeff Moden (11/6/2007)
True enough... and nicely done. I was being a bit sarcastic because the OP didn't want to use a stored procedure... I went a bit overboard with a way to do it in a single query with no declarations 😉Sometimes the requirements these poor folks get just amaze me... :hehe:
You know, even 170 rows is not a big seal.
🙂
And you can never get "overflow" error. :w00t:
_____________
Code for TallyGenerator
November 6, 2007 at 10:32 pm
Yes...Thank you very much...Recursion has some disadvantages. For Example:
Each time we get one record from the DB, it takes n seconds.
Each DB connection/disconnection takes n * 2 seconds.
Allocating memory for a new incidence of the function takes n/10 * iteration seconds
So, if we had 1000 messages in our discussion forum, the equation would look something like this:
(1000 * n) + (1000 * n * 2) + (n/10 * iteration).
In detail:
Record 1: 3 * n seconds
Record 250: 29 * n seconds
Record 500: 53 * n seconds
Record 750: 79 * n seconds
Record 1000: 103 * n seconds
----------------------------
Total = approx 50,000 * n seconds
-----------------------------------
Thanks,
Kannan R
November 6, 2007 at 10:41 pm
??? Recursion has some disadvantages compared to what? And, I'm confused... tell me again why you would use a Factorial function in conjunction with forum messages :blink:
--Jeff Moden
November 6, 2007 at 10:52 pm
Actually, I woluld like to display like heirarchical lists in my work.Because i used Recursion method.
Thanks....
November 7, 2007 at 5:11 am
If you are going to use recursion a lot, then consider moving to SQL Server 2005. You can write recursive SQL using Common Table Experssions (CTEs), which is a lot simpler than the alternatives in SQL Server 2000.
(Unless of course you can exploit Jeff's idea of storing per-prepared results in a table...)
Example of recursive SQL from the SQL 2005 BOL...
USE AdventureWorks;
GO
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS
(
SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
FROM HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
FROM HumanResources.Employee e
INNER JOIN DirectReports d
ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, EmployeeLevel
FROM DirectReports ;
GO
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply