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.
Example
- ;WITH EmployeeCTE AS
- ( SELECT [EmpID]
- ,[EmpName]
- ,[EmpSalary]
- FROM [master].[dbo].[Employee]
- WHERE [EmpSalary]>4000
- )
- SELECT * FROM EmployeeCTE
Now press F5 to execute.
Output
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:
- set nocount on
- Declare @Number int,@Fact int
- set @Fact=1
- set @Number =6;
- WITH Factorial AS
- (
- SELECT
- CASE WHEN @Number<0 THEN NULL ELSE 1
- END N
- UNION all
- SELECT (N+1)
- FROM Factorial
- WHERE N < @Number
- )
- SELECT @Fact = @Fact*N from Factorial
- select @Number as 'Number', @Fact as 'Factorial'
- Print 'The factorial of' +SPACE(1) + cast(@Number as varchar(100)) + SPACE(1) + 'is:' + cast(@Fact as varchar(100))
Output
When you click on the message tab, the following message will be printed:
Output