We can Define stored Procedure in SQL Server as the set of logically group of SQL statement they are grouped to perform a particular task. This article will describe the Stored Procedure in SQL Server and the benefits with stored procedures..
Stored Procedure: We can define stored Procedure in SQLServer as the set of logical group of SQL statements they are grouped to perform a particular task. There are so many benefits behind using a stored procedure. One of
the main benefit behind using a stored procedure is that it increases the query performance of the database. The other benefits behind using the Stored Procedure are listed below.
Benefits Behind Using the Stored Procedure
1. One of the main benefits behind using the stored procedure is that it reduces the amount of information sent to the database server. It can prove itself a more beneficial when the speed of the network is slow. Since if we send the SQL query (statement) which is getting executed repeatedly to the server through network and the same time network gets disconnected, then the execution of the SQL statement would not return the desired results, if the SQL query is not used between Transaction statement and rollback statement is not being used.
2. Need to compile only once when the stored procedure is created. Then after it’s not required to recompile before executing until it is modified and reutilizes the same execution plan while the SQL statements need to be compiled every time whenever it is requested to be executed even if we send the same SQL statement all the time.
3. It facilitates in re-usability of the SQL code as multiple users can reuse it and by multiple clients since we need to just execute the stored procedure at the place of writing the same SQL statement all the time. It also reduces the development time.
4. Stored procedure is helpful in terms of enhancing the security as we can grant permission to the user for executing the stored procedure at the place of giving permission on the tables used that is being used in stored procedure.
5. Sometimes, it is quiet useful to use the database for storing the business logic as a stored procedure since it makes it secured and in case any change is needed in the business logic, then we may need to make changes in the stored procedure only and not in the files contained on the web server.
Let’s see how to write a Stored Procedure in SQL Server
For Example I have created a table called students, below is the structure of the table:
For Example we insert the following data into the above mentioned table:
Now, at the time of writing a Stored Procedure, the first step would be to write the Create Procedure statement as the first statement:
Now, suppose we have to create a Stored Procedure to return a student name
where student_id is given as an input parameter to the stored procedure. Then, the stored Procedure will be something like:
We can collect the name of the student as well in the output parameter of the Stored Procedure. For example:
Note:-/* */ is used to put comments in one or multiple lines within your sql statements
--sign is used to write a comment in a single line
Note: It is not mandatory that a stored procedure will have to return something all the time. There may be a case when a stored procedure doesn't have to returns anything. For example, a stored procedure can be used to Insert, delete or update As a statement For example, the below created stored procedure is used to insert values into the table students_records.
Execution of the Stored Procedure without having an Output Parameter
A stored procedure is used in the SQL Server with the help of "Execute" or "Exec" Keyword. Like, if we Have to execute the stored procedure "Getstudentname", then we have to use the below written statement.
Execution of the Stored Procedure using the Output Parameter
If we have to execute a stored procedure "GetstudentnameInOutputVariable" , then, first of all we will have to declare a variable to collect the output values. Below is the example:
At the end, we can say that a stored procedure does not enhances the possibility of reusing the code and execution plan only, but it increases the performance of the database as well by reducing the traffic of the network by reducing the amount of information that is sent over the network.