T-SQL Stored Procedures: A Guide to Reusability, Security, and Performance

In SQL Server, a stored procedure is a collection of Transact-SQL (T-SQL) statements grouped together under a specific name. It acts like a subroutine or function within the database, allowing you to encapsulate complex database operations into a single unit.

Benefits of Stored Procedures:

• Reusability: Once created, a stored procedure can be executed multiple times with different input values (parameters) if needed, saving you time and effort from writing the same T-SQL code repeatedly.
• Modularization: They break down complex database tasks into smaller, manageable units, improving code organization and readability.
• Security: You can control access to stored procedures, restricting who can execute them in the database, which enhances data security.
• Parameterization: By accepting parameters, stored procedures can perform actions on specific data sets without needing to modify the core logic within the procedure itself. This makes them more flexible and adaptable.
• Performance: In some cases, stored procedures can improve performance by reducing network traffic, as only the procedure name needs to be sent to the server instead of the entire T-SQL code block. However, this is not always guaranteed and depends on the complexity of the procedure and other factors.

You use the CREATE PROCEDURE statement in T-SQL to define a stored procedure. Here's a basic example:

CREATE PROCEDURE UpdateCustomerDiscount(
    @CustomerID INT,
    @NewDiscount DECIMAL(5, 2)
)
AS
BEGIN
    UPDATE Customers
    SET Discount = @NewDiscount
    WHERE CustomerID = @CustomerID;
END;

This procedure takes two parameters: @CustomerID (integer) and @NewDiscount (decimal with two decimal places). It updates the Discount value for a specific customer in the Customers table based on the provided ID and discount amount.

Once created, you can execute a stored procedure using the EXEC statement, followed by the procedure name and any required parameter values:

EXEC UpdateCustomerDiscount @CustomerID = 123, @NewDiscount = 0.10;

This statement calls the UpdateCustomerDiscount procedure, updates the discount for customer ID 123 to 10%, and returns any output values defined within the procedure (if applicable).

This procedure retrieves customer information based on a provided customer ID:

CREATE PROCEDURE GetCustomerDetails(@CustomerID INT)
AS
BEGIN
    SELECT *
    FROM Customers
    WHERE CustomerID = @CustomerID;
END;

Execution:

EXEC GetCustomerDetails @CustomerID = 456;

This procedure inserts a new order record into the Orders table:

CREATE PROCEDURE CreateOrder(
    @CustomerID INT,
    @OrderDate DATE,
    @TotalAmount DECIMAL(10, 2)
)
AS
BEGIN
    INSERT INTO Orders (CustomerID, OrderDate, TotalAmount)
    VALUES (@CustomerID, @OrderDate, @TotalAmount);
END;

EXEC CreateOrder @CustomerID = 789, @OrderDate = '2024-04-09', @TotalAmount = 250.00;

Using Output Parameters:

This procedure updates product quantity and returns the new quantity as an output parameter:

CREATE PROCEDURE UpdateProductQuantity(
    @ProductID INT,
    @QuantitySold INT,
    @NewQuantity OUT INT
)
AS
BEGIN
    UPDATE Products
    SET Quantity = Quantity - @QuantitySold
    WHERE ProductID = @ProductID;

    SET @NewQuantity = (SELECT Quantity FROM Products WHERE ProductID = @ProductID);
END;

DECLARE @NewStock INT;

EXEC UpdateProductQuantity @ProductID = 101, @QuantitySold = 20, @NewQuantity = @NewStock OUTPUT;

SELECT 'New stock level:', @NewStock;

This example demonstrates how to capture the updated quantity and display it after the procedure execution.

Error Handling:

This procedure includes basic error handling to check for non-existent customer IDs:

CREATE PROCEDURE UpdateCustomerDiscountSafe(
    @CustomerID INT,
    @NewDiscount DECIMAL(5, 2)
)
AS
BEGIN
    DECLARE @CustomerExists BIT;

    SELECT @CustomerExists = EXISTS (SELECT 1 FROM Customers WHERE CustomerID = @CustomerID);

    IF NOT @CustomerExists
        BEGIN
            RAISERROR ('Customer with ID %d not found.', 16, 1, @CustomerID);
            RETURN;
        END;

    UPDATE Customers
    SET Discount = @NewDiscount
    WHERE CustomerID = @CustomerID;
END;

This procedure checks if the provided customer ID exists before updating the discount. If not found, it raises an error message and exits.

For straightforward queries that retrieve or update a small amount of data, directly embedding the T-SQL code in your application might suffice. This can keep your code more concise, especially for one-off operations.

Object-Relational Mappers (ORMs):

ORMs like Entity Framework (EF Core for .NET) or Hibernate (for Java) provide a higher-level abstraction for interacting with databases. They map your application objects to database tables, allowing you to write code that focuses on object manipulation instead of raw SQL. This can improve code maintainability and reduce the need for complex stored procedures. However, ORMs might introduce some performance overhead compared to well-optimized stored procedures.

Dynamic SQL:

Dynamic SQL allows you to construct T-SQL statements at runtime based on user input or other dynamic conditions. While powerful for flexible queries, it can be less secure and can make code harder to maintain. Use dynamic SQL cautiously and with proper input validation to avoid security vulnerabilities like SQL injection.

Views:

Views are virtual tables based on underlying tables or other views. They offer a predefined way to present data, potentially simplifying queries in your application and restricting access to certain columns. However, views cannot perform updates or data manipulation on their own.

Choosing the Right Method:

The best approach depends on your specific needs. Here's a general guideline:

• Use stored procedures for complex operations involving multiple statements, parameterization, error handling, or security restrictions.
• Consider embedding T-SQL or using ORMs for simpler queries.
• Use dynamic SQL with caution and for specific use cases.
• Utilize views to present data in a controlled way.