Stored Procedures in SQL

Stored Procedures

Stored Procedures in SQL is an amazing feature provided by Microsoft in SQL. This is the feature that you will find yourself using a lot when you start developing web applications, and reports.

Stored Procedure has several names like proc, storp, sproc, stopro, StoredProc, sp. The stored proc get saved in the data dictionary of the database.

Why StoredProc

Suppose you have a big shopping website. There is a big database or database where all the data of the product information is stored. The table where the product information is saved will be very complex and has hundreds of columns.

Every time when the user comes to the website and searches for a product. There will be a bunch of Select queries running to get the data from the database tables. We put all these queries inside a stored proc and give this stored proc a name.

This stored proc will be a parameterized stored pro that can take some input in the parameter and process the output based on the input. Let’s say the user will search for sports shoes, the sports shoes will be passed as a parameter to the stored proc and SP will run the select queries and find the information for the user.

The stored proc can not be based on select queries but they can be created to perform CRUD operations. The CRUD term stands for Create Read Update and Delete.

The stored proc can create records in the table, read, update, and delete. So overall they can do everything for you.

Let’s see a scenario where you might write a StorPro for inserting data inside a table. When you log in to your shopping website. A table gets created for your cart. Whatever you add into your Cart, that item gets saved into your table by Insert storePro. When you change the quantity, update Proc perform update operation into your cart table.

I hope you get the idea of where you need to use StorPro. This is the time to talk about how to create a Proc.

Note – No one will be able to use your stored procedure if you do not give them the right to use it. You have full control over who can use your stored procedure. Also note, every time you create a new a StoredProc, it actually gets compiled and an execution plan is created for it You will understand more about it further about it when we start creating one.

Before we start discussing Storproc, please install SQL and SSMS if you have not already. We will also be using Adventureworks in this article. So please import Adventurewokrs in your SSMS.

In case you do not want to use Adventureworks, simply go to our last article Computed Columns in SQL and create a table for your by using the queries that have data in it too.

How to create a Stored Procedure

We will be using HumanResource.Department table of Adventureworks in this practice here. Let’s start it in the steps. We will be creating both, the parameterless StorProc and parameterized StorProc. Let’s start with Parameterless first.

Parameterless Stored Procedure

Step 1

Select right database

Use AdventureWorks2019

Step 2

See all the data inside the table

select * from HumanResources.Department

Step 3

Create Stored procedure by running following command.

Create procedure FindAll
As set nocount off
Select * from HumanResources.Department

Step 4

The stored procedure has been created. Not run execute the command to execute the stored procedure.

Execute FindAll

Explanation

As you can see, we ran the select query first and saw the result. After creating a procedure we are getting the same result but this time with a smaller query. This was a simple stored procedure, image how complex the query inside that procedure can be and all you need to do is, execute that Stored Procedure.

Parameterised Stored Procedure

A parameterized Stored procedure is something that takes some input and gives the output according to the input.

Example – In the table above, we have a column named GroupName. That column has several distinct values in it. Suppose we want to see the data for a particular GroupName. How will you do so? Let’s do it, step by step.

Step 1

Use the right database

Use AdventureWorks2019

Step 2

See all the data, seeing the data will help you remember the column names.

select * from HumanResources.Department

Step 3

Create Parameterised Stored Procedure

Create procedure FindByGroupName @GroupName varchar(40)
As set nocount off
Select * from HumanResources.Department where GroupName = @GroupName

Step 4

Execute the procedure with parameter value.

Execute FindByGroupName 'Executive General and Administration'

You can see the out shows the data for only Executive General and Administration. Just like this you can choose the value for your parameter and find different results from a single stored procedure.

In the example of the shopping website the value for the parameter can be shoes and it will show you the result for the shoes.

I hope the article was helpful. In case of any suggestion or feedback, feel free to comment. I would be glad to assist.

Take Care Guys!