MSDN Documentation

Stored Procedures

This section provides reference information for Transact-SQL (T-SQL) stored procedures within the SQL Server Database Engine. Stored procedures are a set of T-SQL statements that are compiled and stored on the server. They can be executed by applications and users to perform specific tasks.

Overview

Stored procedures offer several benefits, including:

• Improved Performance: Pre-compiled and optimized execution plans.
• Reduced Network Traffic: Executing a single stored procedure call instead of multiple individual SQL statements.
• Enhanced Security: Granular permissions can be granted on stored procedures, restricting direct access to underlying tables.
• Code Reusability: Common logic can be encapsulated and called from multiple places.
• Modularity: Simplifies application development and maintenance by breaking down complex operations into smaller, manageable units.

Key Concepts

• Creating Stored Procedures: Using the CREATE PROCEDURE statement.
• Executing Stored Procedures: Using the EXECUTE or EXEC command.
• Parameters: Passing input and output values to and from stored procedures.
• Return Values: Stored procedures can return integer status codes.
• Error Handling: Using TRY...CATCH blocks and system functions like @@ERROR.
• Transaction Management: Controlling the atomicity of operations within a stored procedure.

Syntax Examples

Creating a Simple Stored Procedure

CREATE PROCEDURE GetCustomerOrders
    @CustomerID INT
AS
BEGIN
    SELECT
        o.OrderID,
        o.OrderDate,
        o.TotalAmount
    FROM
        Orders AS o
    WHERE
        o.CustomerID = @CustomerID;
END;
                

Executing a Stored Procedure

EXECUTE GetCustomerOrders @CustomerID = 101;
-- Or simply:
-- EXEC GetCustomerOrders 101;
                

Stored Procedure with Output Parameter

CREATE PROCEDURE GetOrderCountByCustomer
    @CustomerID INT,
    @OrderCount INT OUTPUT
AS
BEGIN
    SELECT @OrderCount = COUNT(OrderID)
    FROM Orders
    WHERE CustomerID = @CustomerID;
END;
                

Executing a Stored Procedure with an Output Parameter

DECLARE @Count INT;
EXEC GetOrderCountByCustomer @CustomerID = 102, @OrderCount = @Count OUTPUT;
SELECT 'Number of orders for Customer 102:', @Count AS OrderCount;
                

System Stored Procedures

SQL Server provides a rich set of built-in system stored procedures that perform administrative and diagnostic tasks. These are typically prefixed with sp_.

• sp_help: Displays information about a database object.
• sp_who: Provides information about current users, processes, and locks.
• sp_configure: Allows viewing and modifying server configuration options.
• sp_rename: Renames a database object.

For a comprehensive list and detailed descriptions of all Transact-SQL syntax and functions, please refer to the Transact-SQL Reference.