Sql Server Creating And Executing Stored Procedures Complete Guide

Step 1: Understanding Stored Procedures

Stored Procedures in SQL Server are precompiled groups of Transact-SQL statements that can be stored in the database and executed by name. They provide the following benefits:

• Improved Performance: Compiled once and can be reused.
• Security: Can restrict unauthorized access to data.
• Reusability: Can be invoked by multiple applications or components.

Step 2: Setting Up SQL Server Environment

Ensure you have access to an SQL Server instance and management tools like SQL Server Management Studio (SSMS).

Step 3: Creating a Stored Procedure

Let’s create a simple stored procedure that retrieves employee details from a sample table.

Step 3.1: Create Sample Table

First, we need a sample table to work with. Here's how you can create a table named Employees:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Department NVARCHAR(50)
);

Step 3.2: Insert Sample Data

Insert some sample data into the Employees table:

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department)
VALUES 
    (1, 'John', 'Doe', 'Sales'),
    (2, 'Jane', 'Smith', 'HR'),
    (3, 'Alice', 'Johnson', 'IT'),
    (4, 'Bob', 'Brown', 'Finance');

Step 3.3: Create the Stored Procedure

Now, let's create a stored procedure named GetEmployeeInfo that retrieves all employees from the Employees table.

CREATE PROCEDURE GetEmployeeInfo
AS
BEGIN
    SELECT EmployeeID, FirstName, LastName, Department
    FROM Employees;
END;

Step 3.4: Verify the Stored Procedure Creation

You can see the list of stored procedures in your database by expanding the Programmability folder in SSMS and then expanding the Stored Procedures folder.

Step 4: Executing the Stored Procedure

You can execute the stored procedure using the EXEC command.

Step 4.1: Execute the Stored Procedure

EXEC GetEmployeeInfo;

You should see the list of employees as output.

Step 5: Creating a Stored Procedure with Parameters

Now, let's create a stored procedure that retrieves details for a specific employee using an EmployeeID.

Step 5.1: Create the Stored Procedure with Parameters

CREATE PROCEDURE GetEmployeeById
    @EmpID INT
AS
BEGIN
    SELECT EmployeeID, FirstName, LastName, Department
    FROM Employees
    WHERE EmployeeID = @EmpID;
END;

Step 5.2: Execute the Stored Procedure with Parameters

To execute the stored procedure and get details for a specific employee, use the following command:

EXEC GetEmployeeById @EmpID = 2;

You should see the details for the employee with EmployeeID 2.

Step 6: Modifying a Stored Procedure

To modify an existing stored procedure, use the ALTER PROCEDURE statement.

Step 6.1: Modify the Stored Procedure

Let’s modify the GetEmployeeById procedure to also include the Department name in the output.

ALTER PROCEDURE GetEmployeeById
    @EmpID INT
AS
BEGIN
    SELECT EmployeeID, FirstName, LastName, Department
    FROM Employees
    WHERE EmployeeID = @EmpID;
END;

In this case, the procedure is already correctly showing the Department, so no change is needed. But the syntax to alter the procedure is shown above.

Step 7: Dropping a Stored Procedure

If you want to delete a stored procedure, you can use the DROP PROCEDURE statement.

Step 7.1: Drop the Stored Procedure

DROP PROCEDURE GetEmployeeById;

This command will remove the GetEmployeeById stored procedure from the database.

Step 8: Handling Errors in Stored Procedures

You can add error handling in a stored procedure using the TRY...CATCH block.

Step 8.1: Create a Stored Procedure with Error Handling

CREATE PROCEDURE GetEmployeeById_ErrorHandling
    @EmpID INT
AS
BEGIN
    BEGIN TRY
        -- Start of your SQL statements
        SELECT EmployeeID, FirstName, LastName, Department
        FROM Employees
        WHERE EmployeeID = @EmpID;

        IF @@ROWCOUNT = 0
        BEGIN
            RAISERROR('No employee found with the specified ID.', 16, 1);
        END
        -- End of your SQL statements
    END TRY
    BEGIN CATCH
        -- Error handling code
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT 
            @ErrorMessage = ERROR_MESSAGE(),
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE();

        -- Print the error message
        PRINT @ErrorMessage;

        -- Raise the error again
        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH
END;

Step 8.2: Execute the Stored Procedure with Error Handling

Try executing with an invalid EmployeeID to see the error handling in action:

EXEC GetEmployeeById_ErrorHandling @EmpID = 10;

This will display an error message indicating that no employee was found with the specified ID.

Conclusion

This comprehensive guide walks you through creating and executing stored procedures in SQL Server, including examples with and without parameters, modifying and dropping procedures, and basic error handling. Always refer to the official SQL Server documentation for more advanced features and best practices.