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 :
CLICK HERE TO SEE MORE EXAMPLE ABOUT SCALAR VALUED FUNCTIONS
