Sql Stored Procedure Examples – Query Examples

Sql Stored Procedure Examples

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:

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;

Result Of The Code : 45

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.

After getting succesful message we can run the below code to get random values. You can enter deferent values.

Each time you run the code, you will receive different values between 20 and 30.
I got the result : 23

Result : 20

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

After you run the code above than you run the code below.

Result: Lists 5 numbers between 20 and 30

Result:Lists 6 numbers between 0 and 49

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.

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.

Result:120

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.

The Usage Of The Procedure

Result: 81

Result: 32

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.

After run the code above we will get successful message. Now we sum any three numbers we want running the code below.

Result: 24

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.

After running the code above we will get succesfull message. Then we can use the procedure like below.

Example 1

Result: 1 (Prime)

Example 2

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.

After running the code above we will get succesful message, than we may run the codes below

Ex1

Result

Ex2

Result

9) Sql Stored Procedure For Fibonacci Sequence

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

Result

10) Sql Fibonacci Sequence Solution 2

Example-1

Result

Example-2

Result

Stored Procedure Examples With Tables On Library Database

TO DOWNLOAD THE SAMPLE LİBRARY DATABASE CLICK

Example 1)The procedure that adds the data to the student table

After running the code above, than if you get successful message you can run the code below.

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

After running the code above, than if you get successful message you can run the code below.

Output: “Successful”

Output: “Error:The book”s PageCount is smaller than 100″

YOU MAY WANT TO SEE OUR ALL EXAMPLES PAGE, THEN CLICK HERE

About the author

admin

Leave a Comment

%d bloggers like this: