Stored Procedures in SQL Server

Stored procedures are precompiled SQL statements or a set of SQL statements stored on the database server. They offer significant advantages in terms of performance, security, and maintainability.

What are Stored Procedures?

A stored procedure is a database object that contains a set of SQL statements that can be executed as a single unit. You can define input and output parameters, and they can perform complex logic, control flow, and interact with other database objects.

Why Use Stored Procedures?

• Performance: Stored procedures are compiled and stored on the server, so they can be executed more efficiently than ad-hoc SQL queries. The execution plan is cached, leading to faster execution on subsequent calls.
• Security: You can grant users permission to execute a stored procedure without granting them direct permission to access the underlying tables. This enhances data security by limiting direct data manipulation.
• Maintainability: Encapsulating logic within stored procedures makes it easier to manage and update. If the logic needs to change, you only need to modify the stored procedure itself, rather than updating multiple application scripts.
• Reusability: Stored procedures can be called from multiple applications or scripts, promoting code reuse and reducing redundancy.
• Reduced Network Traffic: Instead of sending multiple SQL statements over the network, only the command to execute the stored procedure and its parameters are sent.

Creating a Stored Procedure

The basic syntax for creating a stored procedure in SQL Server uses the CREATE PROCEDURE (or CREATE PROC) statement.

CREATE PROCEDURE procedure_name
                    [ @parameter1 datatype = default_value,
                      @parameter2 datatype = default_value OUTPUT ]
                AS
                BEGIN
                    -- SQL statements to be executed
                    -- Example: SELECT statement
                    SELECT *
                    FROM YourTable
                    WHERE Column1 = @parameter1;

                    -- Example: Output parameter assignment
                    @parameter2 = 100;
                END;

Example: Creating a Simple Stored Procedure

This procedure retrieves customer information based on a customer ID.

CREATE PROCEDURE GetCustomerByID
                @CustomerID INT
            AS
            BEGIN
                SET NOCOUNT ON; -- Prevents sending row count messages
                SELECT CustomerID, FirstName, LastName, Email
                FROM Customers
                WHERE CustomerID = @CustomerID;
            END;

Executing a Stored Procedure

You can execute a stored procedure using the EXECUTE (or EXEC) command.

EXECUTE procedure_name [ parameter1_value, parameter2_value, ... ];
EXEC procedure_name @parameter1 = value1, @parameter2 = value2;

Example: Executing the Stored Procedure

-- Execute using positional parameters
            EXEC GetCustomerByID 101;

            -- Execute using named parameters
            EXEC GetCustomerByID @CustomerID = 102;

Output Parameters

Stored procedures can return values through output parameters. These are useful for returning single scalar values, such as the number of rows affected or a status code.

Example: Stored Procedure with Output Parameter

CREATE PROCEDURE GetCustomerCountByCity
                @CityName VARCHAR(50),
                @CustomerCount INT OUTPUT
            AS
            BEGIN
                SET NOCOUNT ON;
                SELECT @CustomerCount = COUNT(*)
                FROM Customers
                WHERE City = @CityName;
            END;

Example: Executing with Output Parameter

DECLARE @Count INT;
EXEC GetCustomerCountByCity @CityName = 'London', @CustomerCount = @Count OUTPUT;
SELECT 'Number of customers in London:' AS Message, @Count AS Count;

Modifying and Dropping Stored Procedures

You can modify existing stored procedures using ALTER PROCEDURE and remove them using DROP PROCEDURE.

ALTER PROCEDURE procedure_name
                    [ @parameter1 datatype = default_value ]
                AS
                BEGIN
                    -- New SQL statements
                END;

DROP PROCEDURE procedure_name;

Error Handling in Stored Procedures

Robust stored procedures should include error handling. You can use TRY...CATCH blocks and the @@ERROR system function.

Example: Basic Error Handling with TRY...CATCH

CREATE PROCEDURE SafeUpdateCustomerEmail
                @CustomerID INT,
                @NewEmail VARCHAR(100)
            AS
            BEGIN
                SET NOCOUNT ON;
                BEGIN TRY
                    UPDATE Customers
                    SET Email = @NewEmail
                    WHERE CustomerID = @CustomerID;

                    SELECT 'Update successful!' AS Status;
                END TRY
                BEGIN CATCH
                    -- Log the error or handle it appropriately
                    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
                    DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
                    DECLARE @ErrorState INT = ERROR_STATE();

                    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
                    SELECT 'Update failed!' AS Status;
                END CATCH
            END;

Best Practices for Stored Procedures

• Use meaningful names for procedures and parameters.
• Declare all variables at the beginning of the procedure.
• Use SET NOCOUNT ON to suppress the count of affected rows.
• Handle errors gracefully using TRY...CATCH blocks.
• Avoid dynamic SQL when possible; if necessary, use sp_executesql for better security and performance.
• Keep procedures focused on a single task.
• Add comments to explain complex logic.
• Test thoroughly under various conditions.