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 |

**CLICK HERE TO SEE MORE EXAMPLE ABOUT SCALAR VALUED FUNCTIONS**