In this post we will create more than 10 procedure examples. With this examples you can learn how to create a procedure, how to create procedure with parameters and how to create procedure with output parameter. Additionally we’ll learn how to run a procedure.
1-) Calling a Stored Procedure To Add Two Numbers With Input Output Parameters Example
In this tutorial we will learn how to add two number and return with output parameter in sql stored procedure. Once we must execute the code below and get succesful message:
|
1 2 3 4 5 |
Create Procedure AddTwoNumber(@p1 int,@p2 int,@Result int output) as Begin Set @Result = @p1+ @p2 End |
Before to execute the procedure we must define a variable to keep the result Than we can execute the Stored Procedure with any values you want and we can learn the result with select statement;
|
1 2 3 |
Declare @r int Execute AddTwoNumber 20,25,@r output Select @r as Result |
Result Of The Code : 45
|
1 2 3 |
Declare @r int Execute AddTwoNumber 5,7,@r output Select @r as Result |
Result Of The Code : 12
2-) The Stored Procedure That Selects Random Number Between the Two Numbers Entered as Parameters In Sql
In this article we’ll learn the procedure that selects random number between the two numbers entered as parameters.
|
1 2 3 4 5 6 7 8 |
Create Procedure Sp_Random_Value @first int, @second int, @result int output As Begin Set @result =Floor(RAND() * (@second-@first))+@first End |
After getting succesful message we can run the below code to get random values. You can enter deferent values.
|
1 2 3 |
Declare @r int Execute Sp_Random_Value 20,30,@r output Select @r |
Each time you run the code, you will receive different values between 20 and 30.
I got the result : 23
|
1 2 3 |
Declare @r int Execute Sp_Random_Value 20,30,@r output Select @r |
Result : 20
|
1 2 3 |
Declare @r int Execute Sp_Random_Value 20,30,@r output Select @r |
3-) Generate Unique Random Numbers In Sql With Stored Procedure
This procedure generates random unique numbers between two numbers. It gets 3 parameters. The first parameter is number you want to list, the second parameter is the start value and the last parameter is the end value. In this proceure we used table variable. With table variable we saved numbers. We checked if the number exists in the tablei if not exists then the number is added to the table variable
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
Create procedure randomGenerate(@unit int,@min int,@max int) as Begin Declare @numbers table(number int) Declare @i int = 0 Declare @number int while (@i<@unit) Begin Set @number = floor(rand()*(@max-@min+1))+@min if(not exists(Select * from @numbers where number = @number)) begin insert into @numbers values(@number) Set @i = @i + 1 end end Select * from @numbers order by 1 End |
After you run the code above than you run the code below.
|
1 |
Execute randomGenerate 5,20,30 |
Result: Lists 5 numbers between 20 and 30
|
1 |
Execute randomGenerate 6,0,49 |
Result:Lists 6 numbers between 0 and 49
|
1 |
Result:Lists 10 numbers between 50 and 100
4-) Calculating Factorial of Given Number with Stored Procedure
In this tutorial we will write “Stored Procedure” calculates the factorial of a number. That is specified at below.
|
1 2 3 4 5 6 7 8 9 10 |
Create procedure factor(@number int) as begin Declare @i int = 1,@result int=1 while (@i<=@number) Begin Set @result = @result * @i Set @i += 1 End Select @result End |
You must execute the code ebove and get succesful message. Then By running the following code, you can calculate the factorial of the number you want.
|
1 |
Execute factor 5 |
Result:120
|
1 |
Execute factor 10 |
Result:3628800
5-) Create A Stored Procedure Calculate Power Of A Number In Sql
In this example we will create a procedure that calculates power of given number.
|
1 2 3 4 5 6 7 8 9 10 |
Create Procedure myPower(@num int,@pow int, @result int output) As Begin Declare @i int = 0; Set @result = 1 while(@i<@pow) Begin Set @result = @result * @num Set @i += 1 End End |
The Usage Of The Procedure
|
1 2 3 |
Declare @result int Execute myPower 3,4,@result output Select @result |
Result: 81
|
1 2 3 |
Declare @result int Execute myPower 2,5,@result output Select @result |
Result: 32
|
1 2 3 |
Declare @result int Execute myPower 5,3,@result output Select @result |
Result: 125
6-) Finding The Sum Of The Three Numbers With Stored Procedure
In this tutorial web William create a Procedure that Sums Three numbers. The numbers will be written by parametter. Lets do it.
|
1 2 3 4 5 6 7 8 |
Create Procedure sumThree @n1 int, @n2 int, @n3 int, @result int output as Begin Set @result = @n1+@n2+@n3 End |
After run the code above we will get successful message. Now we sum any three numbers we want running the code below.
|
1 |
Execute sumThree 5,7,12 |
Result: 24
|
1 |
Execute sumThree 5,17,12 |
Result: 34
7) Stored Procedure To Find A Number Is Prime In Sql
We will write a procedure that finds given number is Prime. The procedure gets an integer parameter and a bit parameter output. If the result parameter’s value is 0 (zero) then the written number is prime, if the result parameter’s value is 1 (one) then the written number is non-prime.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
Create Procedure sp_isPrime (@number int,@result bit output) as Begin Set @result = 1 Declare @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 procedure like below.
Example 1
|
1 2 3 |
Declare @result bit Execute sp_isPrime 11,@result output Select @result |
Result: 1 (Prime)
Example 2
|
1 2 3 |
Declare @result bit Execute sp_isPrime 9,@result output Select @result |
Result: 0 (non-Prime)
8) Stored Procedure To Divide Numbers And Get Divided Value And Remaining Value
In this post we will write a stored Procedure. We will divide numbers than we will get divided value and remaining value by output parameter.
|
1 |
After running the code above we will get succesful message, than we may run the codes below
Ex1
|
1 2 3 |
Declare @d int,@r int Execute sp_Devide 34,7,@d output,@r output Select @d Division,@r Remaining |
Result
Ex2
|
1 2 3 |
Declare @d int,@r int Execute sp_Devide 25,4,@d output,@r output Select @d Division,@r Remaining |
Result
9) Sql Stored Procedure For Fibonacci Sequence
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Create Procedure sp_Fibonacci(@max int) AS BEGIN Declare @numbers table(number int) Declare @n1 int = 0,@n2 int =1,@i int=0,@temp int Insert Into @numbers Values(@n1),(@n2) WHILE (@i<@max-2) BEGIN Insert Into @numbers Values(@n2+@n1) set @temp = @n2 Set @n2 = @n2 + @n1 Set @n1 = @temp Set @i += 1 END Select * from @numbers END |
When you run the code above you will get succesfull message. After you can run the codes below
Example-1
Exec sp_Fibonacci 5
Result
Example-2
|
1 |
Exec sp_Fibonacci 15 |
Result
10) Sql Fibonacci Sequence Solution 2
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
Create Procedure sp_Fibonacci2(@count int) AS BEGIN Declare @numbers TABLE(number int)//Defining table variable Declare @n int,@i int=0 // Define variable for add Insert Into @numbers Values(0),(1) WHILE (@i<@count) BEGIN Select @n = sum(number) from (Select top 2 number from @numbers order by number desc) as T Insert Into @numbers Values(@n) Select @i = count(*) from @numbers END Select * from @numbers END |
Example-1
|
1 |
Exec sp_Fibonacci2 5 |
Result
Example-2
|
1 |
Exec sp_Fibonacci2 15 |
Result
11) Age Calculate With Given Date Parameter
In this post we will create a Stored Procedure that calculates age.
|
1 2 3 4 5 6 7 8 9 10 |
Create Procedure SP_Age_Calculate(@birthdate datetime,@age int output) as Begin Set @age = DATEDIFF(yy,@birthdate,getdate()) End -- First Execute the code above, if you get successful message, then you can learn the age of you entered birthdate Declare @age int Execute SP_Age_Calculate '10.04.1984',@age output Select @age |
Stored Procedure Examples With Tables On Library Database
Example 1)The procedure that adds the data to the student table
|
1 2 3 4 5 6 7 8 9 10 |
Create Procedure Sp_AddStudent( @name varchar(20), @surname varchar(20), @birthdate date, @gender char(1), @class varchar(5)) as Begin Insert Into Students(name,surname,birthdate,gender,class,point) values(@name,@surname,@birthdate,@gender,@class,0) End |
After running the code above, than if you get successful message you can run the code below.
|
1 |
Execute Sp_AddStudent 'Jane', 'Black','05.05.2005','F','9A' |
After running the code above if you get 1 row affected message, You are succeed
Example 2) The procedure adds the book with given parameters to the books table. The books pageCount must be over then 100
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
Create Procedure Sp_AddBook2( @name varchar(90), @pagecount int, @point int, @authorId int, @typeId int, @errorMessage varchar(1000) output) as Begin If(@pagecount>100) Begin INSERT INTO books (name,pagecount,point,authorId,typeId) VALUES(@name,@pagecount,@point,@authorId,@typeId) Set @errorMessage = 'Successful' End Else Begin Set @errorMessage = 'Error:The book''s PageCount is smaller than 100' End End |
After running the code above, than if you get successful message you can run the code below.
|
1 |
Execute Sp_AddBook 'Three Soldiers',150,20,3,2 |
Output: “Successful”
|
1 |
Execute Sp_AddBook 'Three Soldiers',50,20,3,2 |
Output: “Error:The book”s PageCount is smaller than 100″
[…] To See Other Stored Procedure Examples Click […]
[…] To See Other Stored Procedure Examples Click […]
[…] To See Other Stored Procedure Examples Click […]
[…] To See Other Stored Procedure Examples Click […]
[…] To See Other Stored Procedure Examples Click […]
[…] To See Other Stored Procedure Examples Click […]
[…] To See Other Stored Procedure Examples Click […]
[…] To See Other Stored Procedure Examples Click […]