SQL Stored Procedures

What are Stored Procedures?

Stored procedures offer several advantages:

• Performance: Compiled once and reused, reducing execution overhead.
• Security: Control access to data and operations without granting direct table access.
• Modularity: Encapsulate complex logic into single executable units.
• Maintainability: Simplify updates and changes to database logic.
• Reduced Network Traffic: Send only the procedure call and parameters instead of multiple SQL statements.

Creating a Stored Procedure

The basic syntax for creating a stored procedure involves the CREATE PROCEDURE statement. You can define input, output, and input/output parameters.

Example: Simple Stored Procedure

This procedure retrieves customer names based on a given city.

CREATE PROCEDURE usp_GetCustomersByCity
    @CityName NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT CustomerID, CompanyName, ContactName
    FROM Customers
    WHERE City = @CityName;
END;
                

Explanation:

• CREATE PROCEDURE usp_GetCustomersByCity: Defines the procedure name (using a common prefix like usp_ is a good practice).
• @CityName NVARCHAR(50): Declares an input parameter named @CityName.
• SET NOCOUNT ON;: Prevents the sending of "xx rows affected" messages to the client, which can improve performance.
• SELECT ... FROM Customers WHERE City = @CityName;: The core SQL query that the procedure executes.

Executing a Stored Procedure

Stored procedures are executed using the EXEC or EXECUTE command.

Example: Executing the Procedure

EXEC usp_GetCustomersByCity @CityName = 'London';
                

Or using positional parameters:

EXEC usp_GetCustomersByCity 'Berlin';
                

Stored Procedure Parameters

Parameters allow you to pass values into and receive values out of stored procedures.

• Input Parameters: Used to pass data into the procedure (e.g., @CityName in the example above).
• Output Parameters: Used to return a single value from the procedure.
• Input/Output Parameters: Allow a parameter to be passed in and modified, with the modified value returned.

Example: Output Parameter

CREATE PROCEDURE usp_GetCustomerCountByCity
    @CityName NVARCHAR(50),
    @CustomerCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT @CustomerCount = COUNT(CustomerID)
    FROM Customers
    WHERE City = @CityName;
END;
                

Executing with an Output Parameter:

DECLARE @Count INT;
EXEC usp_GetCustomerCountByCity @CityName = 'Paris', @CustomerCount = @Count OUTPUT;
SELECT @Count AS TotalCustomersInParis;
                

Advanced Concepts

Explore topics such as:

• Error Handling (TRY...CATCH blocks)
• Transaction Management (BEGIN TRANSACTION, COMMIT TRANSACTION, ROLLBACK TRANSACTION)
• Dynamic SQL (sp_executesql)
• Cursor Usage (with caution)
• Returning Result Sets
• Stored Procedure Security and Permissions
• Performance Tuning and Indexing Strategies for Procedures

Best Practices

To ensure efficient and maintainable stored procedures:

• Use meaningful names for procedures and parameters.
• Prefer parameterized queries over dynamic SQL whenever possible.
• Implement robust error handling.
• Keep procedures focused on a single task.
• Use SET NOCOUNT ON.
• Avoid cursors if set-based operations can achieve the same result.
• Comment your code thoroughly.