Why do we use stored procedures in SQL Server

Stored Procedure: - A stored procedure in SQL Server is a group of one or more T-SQL statements that act as single unit.

Benefits of using Stored Procedure: - It is always recommended to use stored procedures instead of using individual queries. Let’s see what is the reason behind this recommendation.

Reduced Network Traffic: - The T-SQL commands in the stored procedure are executed as a single batch of code and to execute a stored procedure we just need to call the procedure by passing its name from client to server. While executing a batch of queries, each line of code has to pass through the network between client and server which increase the client/Server network traffic.

Reuse of Code: - In case of repetitive queries we should include that batch of code into a stored procedure, in that way, we need not to write same query again and again.

Easier Maintenance:- In case of using stored procedures in our front end applications, if any business logic change comes in, that can be easily done at database end by just altering the definition of the stored procedure. In that way, we need not to modify our code at front end and redeploy the application.

Improved Performance: - A stored procedure complies first time when it is executed but at later stage it doesn’t complies again and again. When it is executed first time, it creates execution plan and store in the cache and query process do not create the execution plan again and again when it is executed, hence it takes lesser time to process the stored procedure.

Syntax of Creating a stored procedure:-

Using below script, I have created a very basic structure of a stored procedure. Lets try to understand each line of the below script.

Create Procedure Usp_Get_EmpDetail(@Eid int) –--Line 1

As---Line 2

Begin---Line 3

      select * from Employee where Eid=@Eid ---Line 4

end---Line 5

I have given numbering to each line of the script, first line starts with “Create procedure” followed by the relevant name that we want to give our stored procedure, in our case “Usp_get_EmpDetail”. In bracket I have declared an integer type input parameter (@Eid) while will be required to execute the procedure.

Line 2 is starting with “As” that is followed by “Begin” and “End” statement, between “Begin” and “End” we define the body or structure of the stored procedure. In our case body of stored procedure is containing only one T-SQL statement.

How to execute a Stored Procedure:- Below is the syntax of executing a stored procedure.

Execute Usp_get_EmpDetail 102

The stored procedure we created earlier has a input parameter, hence we will write above statement to execute the stored procedure with input parameter, in my case I have passed @Eid value 102. Below is the screenshot for your reference.

I have touched very basic area of stored procedure in this current post, I will touch base the advanced section of stored procedure in my next post.