Stored Procedures

Introduction

In this article, I will explain the stored procedure, how to create, execute and alter stored procedures and why to use stored procedures, and the advantages of stored procedures. 

Stored Procedure in SQL Server 

Stored Procedures are coding blocks in the database server. A stored procedure is a group of Transact-SQL statements compiled into a single execution plan. It is a pre-compiled entity, i.e., it is compiled at once and can be used repeatedly. With the help of stored procedures, a group of SQL statements can be executed sequentially. To supply data to the procedure, we must have to use parameters in the procedure.

Stored procedures use the parameters mapping concept. The front-end and procedure parameters' names, types, and directions must be the same in parameter mapping. The front-end parameter length should be less than or equal to the procedure parameter length (only one can map parameters). To return any value from the procedure, we use a return statement.

How to create a stored procedure?

create procedure insertData
(@RollNo int,
@Name varchar(50),
@Fees float)

as
begin
insert into student values(@RollNo,@Name,@Fees)
select * from student

end

How to execute a stored procedure?

exec insertData 8, 'Mahesh', 5600

The output of the above-stored procedure

Modifying a Stored Procedure

alter procedure insertData
(@RollNo int,
@Name varchar(50),
@Fees float)

as
begin
insert into student values(@RollNo,@Name,@Fees)
select * from student

end

Why use stored procedures?

You usually write SQL statements, like select, inserts, and updates, to access your data from the database. If you use the same query repeatedly, putting it into a stored procedure will make sense.

Every time you write a query, it is parsed in the database. If you have written a stored procedure for it, it will be parsed once and can be executed N number of times.

Stored procedures can also improve performance. All the conditional logic is written into a stored procedure, a single execution block on the database server.

Advantages of stored procedure

Modular programming

Stored Procedures are coding blocks in the database server. It is a pre-compiled entity, i.e., it is compiled at once and can be used repeatedly.

Performance

Stored procedures provide faster code execution and reduce network traffic.

Faster execution

Stored procedures are parsed and optimized as soon as they are created, and the stored procedure is stored in memory. This means it will execute much faster than sending many lines of SQL code from your application to the SQL Ser er. Doing that requires SQL Server to compile and optimize your SQL code every time it runs.

Reduced network traffic

Sending many lines of SQL code over the network to your SQL Server will impact network performance. This is especially true if you have hundreds of lines of SQL code and lots of activity on your application. Running the code on the SQL Server (as a stored procedure) eliminates the need to send this code over the network. The only network traffic will be the parameters supplied and any query results.

Security

Users can execute a stored procedure without executing any of the statements directly.

A stored procedure can provide advanced database functionality for users who wouldn't usually have access to these tasks, but this functionality is made available tightly.

Conclusion

I hope this article might have helped you understand stored procedures. Your feedback and constructive contributions are welcome. Please feel free to contact me for feedback or comments you may have about this article.