SQL Server –What are the Stored Procedures in SQL

SQL says that stored procedures are nothing but they are a group of one or more Transact-SQL statements compiled into a single execution plan which are stored in the database data dictionary. 

In more easy words, we can say that stored procedures are used to bind SQL queries together in a transaction and interface with the outside world. They are very important feature of SQL because they are very helpful in controlling access to data, preserving data integrity and improving productivity.

Furthermore, stored procedures can have consolidate and centralize data logic that was originally implemented in applications. Extensive or complex processing that requires execution of several SQL statements is moved into stored procedures and all applications call the procedures. One can use nested stored procedures by executing one stored procedure from within another.

In Microsoft SQL Server, stored procedures return data in four ways:

1. Output parameters, which can return either data (such as an integer or character value) or a cursor variable (cursors are result sets that can be retrieved one row at a time).
2. Return codes, which are always an integer value.
3. A result set for each SELECT statement contained in the stored procedure or any other stored procedures called by the stored procedure.
4. A global cursor that can be referenced outside of that stored procedure.

A stored procedure can accept parameters that are passed to it and perform the defined business operations and logic. We can write a stored procedure once and then call it again and again from different parts of an application or even more than one or more application. Stored procedures can also improve performance. Many tasks are implemented as a series of SQL statements. Conditional logic applied to the results of the first SQL statements determines which subsequent SQL statements are executed.

Stored procedures can also shield users from needing to know the details of the tables in the database because a set of stored procedures supports all of the business functions users need to perform, users never need to access the tables directly; they can just execute the stored procedures that model the business processes with which they are familiar.

Benefits of Stored Procedures

By using SP, it also give us a guarantee what execution plan is used for the query. It will not be affected by any programming language or driver. That's why sometimes a piece of query runs well in the db SQL management tool but runs poorly in the application. In addition, SPs reduce the db calls but not increase.
There are lot of benefits of SQL stored procedures in term of modular programming, faster execution, reduce network traffic and security mechanism such as mentioned below:

Modular Programming	They are created once in the database and call it any number of times by the programs. They can be easily modified independently of the program source code and there will no need in the business application.
Faster Execution	They are parsed and optimized when they are first executed, and a compiled version of the stored procedure remains in memory cache for later use. This means the stored procedure does not need to be reparsed and re-optimized with each use resulting in much faster execution times.
Reduce Network Traffic	Stored procedures can have many individual SQL queries but can be executed with a single statement. This allows us to reduce the number and size of calls from the client to server.
Security Mechanism	Users can be granted permission to execute a stored procedure even if they do not have permission to execute the procedure's statements directly.
protection from SQL injection attacks	Stored procedures can be used to protect against injection attacks. Stored procedure parameters will be treated as data even if an attacker inserts SQL commands. Also, some DBMSs will check the parameter's type. A stored procedure that in turn generates dynamic SQL using the input is however still vulnerable to SQL injections unless proper precautions are taken.