This post contains more than 5 units scalar valued function examples. With this examples you can learn how to create a scalar valued function.
1) Function To Add Two Numbers In Sql Server Example
Hi! In this tutorial we will learn how to add two numbers in sql server by function and how we can use the function. We must execute the code below and get succesful message than we can use that function we want.
1 2 3 4 5 6 7 8 |
Create function fn_sum(@p1 int,@p2 int) returns int as Begin Declare @result int Set @result = @p1 + @p2 Return @result End |
Solution 2
1 2 3 4 5 6 |
Create function fn_sum(@p1 int,@p2 int) returns int as Begin Return @p1 + @p2 End |
The function’s usage types;
The basic usage:
1 |
Select dbo.fn_sum(23,34) |
Usage in a Query;
1 |
Select *,dbo.fn_sum(pageCount,point) as Result from books |
2) Function to Find Factorial in Sql
In this tutorial we will learn how to Create sql function to calculate the factorial with example.
1 2 3 4 5 6 7 8 9 10 11 |
Create function factor(@number int) returns int as begin Declare @i int = 1,@result int=1 while (@i<=@number) Begin Set @result = @result * @i Set @i += 1 End return @result End |
First you must run the code ebove. Then you can calculate any number of factorial with following code.
1 |
Select dbo.factor(10) |
Result::3628800
3) Create Function Like Replicate Not Using Replicate In Sql
In this tutorial we will create a function.This function runs like replicate function. But we will not use the sql replicate function in this function code. If you wonder how you can follow the post.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
Create function MyReplicate(@character varchar(max),@value int) returns varchar(max) as Begin Declare @i int,@result varchar(max) Set @i = 0 Set @result = '' While (@i<@value) Begin Set @result = @result + @character Set @i = @i + 1 End return @result End |
To Run The Function :
1 |
Select dbo.MyReplicate('a',5) |
Result:
aaaaa
Example 2:
1 |
Select dbo.MyReplicate('QS--',15) |
Result:
QS–QS–QS–QS–QS–QS–QS–QS–QS–QS–QS–QS–QS–QS–QS–
4) Create A Function Calculate Power Of A Number In Sql
In this example we will create a function that returns power of given number. Sql already has a function named power for this process. But we will create the function ourselves.
1 2 3 4 5 6 7 8 9 10 |
Create Function MyPower(@num int,@pow int) returns int as Begin Declare @i int = 0,@result int = 1 while(@i<@pow) Begin Set @result = @result * @num Set @i += 1 End return @result End |
After running the above code, you can run the codes below and calculate different number’s power.
Sample1
1 |
Select dbo.MyPower(3,4) |
Result : 81
Sample2
1 |
Select dbo.MyPower(2,3) |
Result : 8
Sample3
1 |
Select dbo.MyPower(5,2) |
Result : 25
Sample4
1 |
Select dbo.MyPower(6,4) |
Result : 1296
5) Create Function Finding the Number Remaining From The Division of Two Numbers In Sql Without Using Mod
In this example we will create a funciton that finds the number remaining from the division of two numbers. In sql normally used % operator for finding remaining value. We will not use % operator in the function. We will do our own function.
1 2 3 4 5 6 7 8 9 |
Create Function myMod(@n1 int, @n2 int) returns int as Begin While(@n1>=@n2) Begin Set @n1 = @n1 - @n2 End return @n1 end |
When we run the code above we will get succesfull message. Then we can calculate different number’s remaining values.
Example
1 |
Select dbo.myMod(6,5) |
Result : 1
Example
1 |
Select dbo.myMod(10,5) |
Result : 0
Example
1 |
Select dbo.myMod(20,12) |
Result : 8
Example
1 |
Select dbo.myMod(3,5) |
Result : 3
Example in a Query
1 |
Select top 5 pageCount,dbo.myMod(pageCount,10) from books |
Result :

6) Function To Find A Number Is Prime In Sql
We will write a funciton that finds given number is Prime. The function gets a number parameter and return bit value (1 or 0). If returns 1 that means the number is prime else returns 0 then the number is non-prime
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
Create function isPrime (@number int) returns int as Begin Declare @result bit = 1,@i int = 2 While (@i<@number) Begin if(@number % @i = 0) Begin Set @result = 0 break End Set @i += 1 End return @result End |
After running the code above we will get succesfull message. Then we can use the function like below.
Example 1:
1 |
Select dbo.isPrime(8) |
Result:0
Example 2:
1 |
Select dbo.isPrime(7) |
Result:1
Not: We can use the function in a query
Example 3
List all books and whether pageCount is Prime Or not
1 |
Select *, dbo.isPrime(pageCount) as Prime from books |
Example 4
List the books which pageCount is prime
1 |
Select * from books where dbo.isPrime(pageCount) = 1 |
7) Create A Function Merge Two String And Returns It
We will create a function that merge two string value and returns it. It is a simple example. There is a function in sql merges two values named concat. We use + operator to merge two strings.
1 2 3 4 5 6 7 |
Create function myMerge(@v1 varchar(30),@v2 varchar(30)) returns varchar(max) as Begin return @v1 + ' '+@v2 End |
After running the code above we will get succesfull message than we can run the code below:
Example
1 |
Select dbo.myMerge('Mike','Brown') |
Result: Mike Brown
Example in a query
1 |
Select dbo.myMerge(name,surname) from students |
Result:

[…] CLICK HERE TO SEE MORE EXAMPLE ABOUT SCALAR VALUED FUNCTIONS […]
[…] CLICK HERE TO SEE MORE EXAMPLE ABOUT SCALAR VALUED FUNCTIONS […]
[…] CLICK HERE TO SEE MORE EXAMPLE ABOUT SCALAR VALUED FUNCTIONS […]
[…] CLICK HERE TO SEE MORE EXAMPLE ABOUT SCALAR VALUED FUNCTIONS […]
[…] CLICK HERE TO SEE MORE EXAMPLE ABOUT SCALAR VALUED FUNCTIONS […]
[…] CLICK HERE TO SEE MORE EXAMPLE ABOUT SCALAR VALUED FUNCTIONS […]
[…] CLICK HERE TO SEE MORE EXAMPLE ABOUT SCALAR VALUED FUNCTIONS […]