SQL Server Documentation

Stored Procedures

A stored procedure is a collection of one or more Transact-SQL statements that are grouped together to perform a specific task. Stored procedures offer several benefits, including improved performance, better security, and modularity for database operations.

Benefits of Stored Procedures

• Performance: Stored procedures are compiled and stored in the database server's memory, which can lead to faster execution compared to ad-hoc SQL queries.
• Reduced Network Traffic: Instead of sending multiple SQL statements over the network, only the call to the stored procedure needs to be sent.
• Security: Permissions can be granted to execute stored procedures, allowing users to perform specific actions without granting them direct access to underlying tables.
• Modularity and Reusability: Stored procedures encapsulate business logic, making it easier to maintain and reuse code across different applications.
• Data Integrity: Stored procedures can enforce data validation rules and business logic consistently.

Creating a Stored Procedure

You can create a stored procedure using the CREATE PROCEDURE statement. The following example demonstrates creating a simple stored procedure to select all customers from a hypothetical Customers table.

CREATE PROCEDURE GetCustomers
AS
BEGIN
    -- Select all customers from the Customers table
    SELECT CustomerID, CompanyName, ContactName
    FROM Customers;
END;
                    

Executing a Stored Procedure

You can execute a stored procedure using the EXECUTE or EXEC command, or by simply naming the procedure.

EXECUTE GetCustomers;

EXEC GetCustomers;

GetCustomers; -- Simpler execution without EXEC keyword
                    

Stored Procedures with Parameters

Stored procedures can accept input parameters, allowing for dynamic execution based on provided values.

CREATE PROCEDURE GetCustomerByID
    @CustomerID INT
AS
BEGIN
    -- Select customer by ID
    SELECT CustomerID, CompanyName, ContactName
    FROM Customers
    WHERE CustomerID = @CustomerID;
END;
                    

EXEC GetCustomerByID @CustomerID = 1;
                    

Output Parameters

Stored procedures can also return values using output parameters.

CREATE PROCEDURE GetCustomerCount
    @TotalCount INT OUTPUT
AS
BEGIN
    SELECT @TotalCount = COUNT(*) FROM Customers;
END;
                    

DECLARE @Count INT;
EXEC GetCustomerCount @TotalCount = @Count OUTPUT;
SELECT @Count AS CustomerCount;
                    

Error Handling and Return Values

Stored procedures can return status codes to indicate success or failure, and can implement error handling logic.

CREATE PROCEDURE AddCustomer
    @CompanyName NVARCHAR(100),
    @ContactName NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        INSERT INTO Customers (CompanyName, ContactName)
        VALUES (@CompanyName, @ContactName);
        RETURN 0; -- Success
    END CATCH
        -- Log error or handle it appropriately
        RETURN -1; -- Failure
    END CATCH
END;
                    

For more advanced features, including dynamic SQL, transaction management, and cursor usage, please refer to the detailed Transact-SQL documentation.