Calculating Factorial of a Number in SQL Server 2012

Here, I have constructed a query that provides a solution in SQL Server to determine the factorial of a specified number. If you have programmed in languages like C, C++ or other languages then you are probably familiar with the word factorial. Transact-SQL also gives you this option to repeat the expression using CTE (Common Table Expression). A factorial is denoted by n!. The factorial of a number is defined as n! = 1 * 2 * ... * n.
 
Let's have a look at a practical example. The example is developed in SQL Server 2012 using the SQL Server Management Studio.
 

SQL Common Table Expression (CTE)

 
SQL Server has a very powerful feature that has been added for the programmers' benefit: Common Table Expression (CTE).  Common Table Expressions, or CTE, are a new construct introduced in Microsoft SQL Server 2005 that offers a more readable form of the derived table that can be declared once and referenced multiple times in a query.
 
We have a simple table Employeein our database.
 
Employee-table-in-Sql-Server.jpg
 
Example
  1. ;WITH EmployeeCTE  AS  
  2. SELECT  [EmpID]  
  3.       ,[EmpName]  
  4.       ,[EmpSalary]  
  5. FROM [master].[dbo].[Employee]  
  6.   WHERE [EmpSalary]>4000  
  7. )  
  8. SELECT * FROM EmployeeCTE  
Now press F5 to execute.
 
Output
 
CTE in Sql Server
 

Calculating Factorial of Numbers using CTE

 
The example of factorial numbers should look as follows:
 
0! = 1
1! = 1
2! = 2
3! = 6
4! = 24
 
The following code defines how to find the factorial of a number:
  1. set nocount on  
  2. Declare @Number int,@Fact int  
  3. set @Fact=1  
  4. set @Number =6; -- To Find Factorial of number  
  5. WITH Factorial AS -- Defined Common Table Expression  
  6. (  
  7. SELECT  
  8. CASE WHEN @Number<0 THEN NULL ELSE 1  -- To check if number is 0 it will return 1  
  9. END N  
  10. UNION all  
  11. SELECT (N+1)    
  12. FROM Factorial  
  13. WHERE N < @Number  -- To check factorial number with increment number  
  14. )  
  15. SELECT @Fact = @Fact*N from Factorial             -- To repeat the process  
  16. select @Number as 'Number', @Fact as 'Factorial' -- To show result  
  17. Print  'The factorial of' +SPACE(1) + cast(@Number as varchar(100))  + SPACE(1) + 'is:' + cast(@Fact as varchar(100))  -- To print a message  
Output
 
Calculating Factorial of Numbers using CTE 
 
When you click on the message tab, the following message will be printed:
 
Output
 
Calculating Factorial of Numbers using CTE 


Similar Articles