MSDN Documentation

SQL Stored Procedures

Stored procedures are a set of SQL statements that are compiled and stored on the database server. They can be executed by applications or other SQL statements. Stored procedures offer several benefits, including improved performance, enhanced security, and modularity.

What are Stored Procedures?

A stored procedure is a prepared SQL code that has been created and stored in the database. When you want to execute a familiar or often-used SQL statement, you can call the stored procedure by name. This reduces network traffic and can provide a performance boost.

Benefits of Stored Procedures

• Performance: Stored procedures are compiled and optimized by the database engine, leading to faster execution compared to ad-hoc SQL queries.
• Modularity: Complex logic can be encapsulated within a stored procedure, making it easier to manage and reuse.
• Security: Permissions can be granted to execute a stored procedure without granting direct access to the underlying tables, enhancing data security.
• Reduced Network Traffic: Instead of sending multiple SQL statements over the network, only the stored procedure name and its parameters are sent.
• Maintainability: Changes to database logic can be made within the stored procedure without requiring changes to the client applications that call them.

Creating a Stored Procedure

The syntax for creating a stored procedure varies slightly depending on the specific SQL database system (e.g., SQL Server, MySQL, PostgreSQL). Here's a general example using T-SQL syntax (common in SQL Server):

CREATE PROCEDURE GetCustomerDetails
    @CustomerID INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT CustomerID, CompanyName, ContactName, City
    FROM Customers
    WHERE CustomerID = @CustomerID;
END;
                    

In this example:

• CREATE PROCEDURE GetCustomerDetails: Defines the name of the stored procedure.
• @CustomerID INT: Declares an input parameter named @CustomerID of integer type.
• AS BEGIN ... END;: Encloses the body of the stored procedure.
• SET NOCOUNT ON;: Prevents the message indicating the number of rows affected by a Transact-SQL statement from being returned as part of the results.
• SELECT ... FROM Customers WHERE CustomerID = @CustomerID;: The core SQL query that retrieves data based on the input parameter.

Executing a Stored Procedure

To execute the stored procedure created above, you would use the EXECUTE or EXEC command:

EXECUTE GetCustomerDetails @CustomerID = 10;
-- Or simply:
EXEC GetCustomerDetails 10;
                    

Stored Procedures with Output Parameters

Stored procedures can also return values through output parameters.

CREATE PROCEDURE GetCustomerCountByCity
    @CityName NVARCHAR(50),
    @CustomerCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @CustomerCount = COUNT(*)
    FROM Customers
    WHERE City = @CityName;
END;
                    

And its execution:

DECLARE @Count INT;
EXEC GetCustomerCountByCity @CityName = 'London', @CustomerCount = @Count OUTPUT;
SELECT @Count AS NumberOfCustomers;
                    

Stored Procedures vs. Functions

While both stored procedures and functions contain SQL logic, they have key differences:

• Return Values: Functions must return a value (scalar or table), whereas stored procedures can return zero or more values via output parameters or result sets.
• Execution Context: Functions can be called within SQL statements (like SELECT), while stored procedures are typically executed as standalone statements using EXEC.
• DML Operations: Stored procedures can perform Data Manipulation Language (DML) operations like INSERT, UPDATE, and DELETE, which is generally not allowed within scalar functions.

Advanced Concepts

• Error Handling within Stored Procedures (e.g., TRY...CATCH blocks).
• Transaction Management.
• Dynamic SQL.
• Cursor usage.

Refer to the specific documentation for your database system for detailed syntax and advanced features.