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.

Best Practices for Stored Procedures

• Use meaningful names: Choose names that clearly describe the purpose of the stored procedure.

• Document your code: Add comments to explain the logic of the stored procedure.

• Handle errors gracefully: Use TRY...CATCH blocks to handle potential errors.

• Validate input parameters: Ensure that input parameters are valid before using them in SQL statements.

• Use transactions: Use transactions to ensure data consistency.

• Keep stored procedures short and focused: Break down complex tasks into smaller, more manageable stored procedures.

• Regularly review and optimize stored procedures: Identify and address performance bottlenecks.

• Implement proper security measures: Protect stored procedures from unauthorized access.

Considerations When Using Stored Procedures 

While stored procedures offer numerous benefits, there are also some considerations to keep in mind:

• Debugging: Debugging stored procedures can be more challenging than debugging ad-hoc SQL queries. Database management systems often provide debugging tools, but they may not be as user-friendly as those available for other programming languages.

• Version Control: Managing versions of stored procedures can be complex, especially in large database environments. It is important to have a robust version control system in place to track changes to stored procedures.

• Overhead: While stored procedures generally improve performance, there is some overhead associated with creating and managing them. This overhead is usually minimal, but it is important to consider it when designing database applications.

• Security Risks: If not properly secured, stored procedures can be exploited by attackers to gain unauthorized access to the database. It is important to carefully review the security implications of stored procedures and implement appropriate security measures.

Disadvantage of using Stored Procedures

There are some disadvantages of stored procedures which are given below:  

1. Debugging large stored procedures can be very difficult in case of any failure.
2. Stored procedure languages are quite often vendor-specific. Switching to another vendor's database most likely requires rewriting any existing stored procedures.
3. Stored procedure languages from different vendors have different levels of sophistication.
4. Tool support for writing and debugging stored procedures is often not as good as for other programming languages, but this differs between vendors and languages.
5. Changes to stored procedures are more difficult to keep track of within a version control system than other code. 
6. Changes must be reproduced as scripts to be stored in the project history to be included, and differences in procedures can be more difficult to merge and track correctly.
7. Stored procedures can grow into an unmaintainable mess if not written carefully. While this is true of any language, it's especially true of SQL server stored procedures.

A SQL Server stored procedure is created with the Transact-SQL CREATE PROCEDURE statement and can be modified with the ALTER PROCEDURE statement. The stored procedure definition contains two primary components: the specification of the procedure name and its parameters, and the body of the procedure, which contains Transact-SQL statements that perform the procedure's operations. 

Working with Stored Procedures

As we know that they are nothing more than stored SQL code that we would like to use over and over again. To understand the functionality of stored procedures, we can easily differentiate it in the following steps-

1. Create Stored Procedure
2. Execute Stored Procedure
3. Modify or Alter Stored Procedure
4. Delete Stored Procedure

Where can we use  Stored Procedures?

Database usually is the biggest investment in the solution.  You will probably be ending up using something that is specific to your database if you want to get the best performance from it, which means running your system at a lower (machine) cost and faster-

1. If you have a good balance of which parts of your business logic goes inside your SPs, you will be in a good place. 
2. If you want to make your system fast and reliable, try to use as much db features as possible. Coding in a database independent way is totally waste until the day you want to change database. 
3. If you want to utilise extensively your db features in your application, as a result there will be lot of unnecessary DB calls which itself will result in other issues e.g db sessions, connection leakages and so on.

Conclusion

We can say that stored procedures are like small programs in SQL Server. They can be simple as a select statement or as more complex based on our needs as a long script (DML statements) that adds, deletes, updates and/or reads data from multiple tables in a database. We can use transactions within them. They can be easily scheduled to run at the specific time through SQL Server Agent. Stored procedure takes both input and output parameters based on the requirement.

Learn more on another feature of SQL as:

1. SQL - Stored Procedure
2. SQL - Create Stored Procedure
3. SQL - Execute Stored Procedure
4. SQL - Alter Stored Procedure
5. SQL - Delete Stored Procedure
6. SQL - Views
7. SQL – Stored Procedure Vs Ad-Hoc (In-Line)
8. SQL - Merge Statement
9. SQL - Functions
10. SQL - Cursors

References

https://technet.microsoft.com/en-us/library/aa174792(v=sql.80).aspx