SQL Server Stored Procedures

Stored procedures are a set of Transact-SQL statements grouped together to perform a specific task. They offer significant benefits in terms of performance, security, and maintainability when developing applications that interact with SQL Server.

What is a Stored Procedure?

A stored procedure is a compiled collection of one or more Transact-SQL statements that can be executed as a single unit. They are stored on the database server, allowing for efficient execution and reuse.

Benefits of Stored Procedures

Performance: Stored procedures are compiled once and stored in the database's memory, leading to faster execution compared to ad-hoc SQL queries.
Reduced Network Traffic: Instead of sending multiple SQL statements over the network, only the stored procedure name and its parameters are sent.
Reusability: Stored procedures can be called from multiple applications or by multiple users, promoting code reuse.
Security: Stored procedures can grant specific permissions to users, allowing them to execute the procedure without having direct access to the underlying tables.
Maintainability: Changes to business logic can be made within the stored procedure without affecting the application code directly, as long as the procedure's interface remains the same.
Modularity: They help in breaking down complex database operations into smaller, manageable units.

Creating a Stored Procedure

The CREATE PROCEDURE statement is used to define a new stored procedure. You can include input parameters, output parameters, and a body of Transact-SQL statements.

CREATE PROCEDURE usp_GetCustomerOrders
                    @CustomerID INT
                AS
                BEGIN
                    -- SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client.
                    SET NOCOUNT ON;

                    -- Select orders for the specified customer
                    SELECT OrderID, OrderDate, TotalAmount
                    FROM Sales.Orders
                    WHERE CustomerID = @CustomerID;
                END;

Executing a Stored Procedure

The EXECUTE or EXEC command is used to run a stored procedure.

EXEC usp_GetCustomerOrders @CustomerID = '123';

Modifying and Dropping Stored Procedures

You can modify an existing stored procedure using ALTER PROCEDURE and remove it using DROP PROCEDURE.

ALTER PROCEDURE usp_GetCustomerOrders
                    @CustomerID INT,
                    @OrderDateStart DATE OUTPUT
                AS
                BEGIN
                    SET NOCOUNT ON;

                    SELECT OrderID, OrderDate, TotalAmount
                    FROM Sales.Orders
                    WHERE CustomerID = @CustomerID;

                    -- Example of an OUTPUT parameter
                    SELECT @OrderDateStart = MAX(OrderDate)
                    FROM Sales.Orders
                    WHERE CustomerID = @CustomerID;
                END;

DROP PROCEDURE usp_GetCustomerOrders;

Parameters

Stored procedures can accept input parameters and return output parameters. Parameters make stored procedures flexible and dynamic.

CREATE PROCEDURE usp_AddProduct
                    @ProductName VARCHAR(100),
                    @Price DECIMAL(10,2),
                    @ProductID INT OUTPUT
                AS
                BEGIN
                    SET NOCOUNT ON;

                    INSERT INTO Production.Products (ProductName, UnitPrice)
                    VALUES (@ProductName, @Price);

                    -- Get the newly generated ProductID
                    SET @ProductID = SCOPE_IDENTITY();
                END;

Calling a Stored Procedure with Output Parameters

When calling a procedure with output parameters, you need to declare a variable to hold the output value.

DECLARE @NewProductID INT;
                EXEC usp_AddProduct @ProductName = 'New Gadget', @Price = 29.99, @ProductID = @NewProductID OUTPUT;
                SELECT 'New Product ID:', @NewProductID;

Error Handling in Stored Procedures

Robust stored procedures should include error handling to gracefully manage issues during execution.

CREATE PROCEDURE usp_UpdateProductPrice
                    @ProductID INT,
                    @NewPrice DECIMAL(10,2)
                AS
                BEGIN
                    SET NOCOUNT ON;
                    BEGIN TRY
                        UPDATE Production.Products
                        SET UnitPrice = @NewPrice
                        WHERE ProductID = @ProductID;
                        SELECT 'Price updated successfully.' AS Status;
                    END TRY
                    BEGIN CATCH
                        -- Log the error or return an error message
                        SELECT
                            'Error updating price.' AS Status,
                            ERROR_NUMBER() AS ErrorNumber,
                            ERROR_MESSAGE() AS ErrorMessage;
                    END CATCH
                END;

Mastering stored procedures is a crucial step in efficient SQL Server development. Explore these concepts further to build powerful and maintainable database solutions.

SQL Server Tutorials