SQL Server T-SQL Stored Procedures

Stored Procedures

Stored procedures are a set of T-SQL statements that are compiled and stored in the database. They can be executed by name and can accept parameters, making them powerful tools for encapsulating logic, improving performance, and enhancing security.

What are Stored Procedures?

A stored procedure is a transactional integral part of a SQL Server database. It is a precompiled set of one or more T-SQL statements that are stored on the server. You can call the stored procedure by name and pass parameters to it. This offers several advantages over sending individual T-SQL statements.

Advantages of Stored Procedures

Performance: Stored procedures are compiled and optimized the first time they are executed. Subsequent executions are faster as the execution plan is cached.
Reduced Network Traffic: Instead of sending multiple T-SQL statements over the network, you only send the name of the stored procedure and its parameters.
Reusability: The same stored procedure can be called from multiple applications or by multiple users, promoting code reuse.
Security: You can grant execute permissions on a stored procedure without granting permissions on the underlying tables, providing a layer of abstraction and security.
Maintainability: Changes to the database schema can be made by modifying the stored procedure, without requiring changes to the calling applications, as long as the procedure's interface remains the same.

Creating a Stored Procedure

The syntax for creating a stored procedure uses the CREATE PROCEDURE or CREATE PROC statement.


CREATE PROCEDURE usp_GetEmployeeDetails
    @EmployeeID INT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT
        EmployeeID,
        FirstName,
        LastName,
        Department,
        Salary
    FROM
        Employees
    WHERE
        EmployeeID = @EmployeeID;
END;

Executing a Stored Procedure

Stored procedures can be executed using the EXECUTE or EXEC statement.


EXECUTE usp_GetEmployeeDetails @EmployeeID = 101;

-- Or simply:
EXEC usp_GetEmployeeDetails 101;

Modifying and Dropping Stored Procedures

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


-- Altering a stored procedure (example: adding a new column to select)
ALTER PROCEDURE usp_GetEmployeeDetails
    @EmployeeID INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        EmployeeID,
        FirstName,
        LastName,
        Department,
        Salary,
        HireDate -- New column added
    FROM
        Employees
    WHERE
        EmployeeID = @EmployeeID;
END;

-- Dropping a stored procedure
DROP PROCEDURE usp_GetEmployeeDetails;

Important Note:

Always consider the impact of changes to stored procedures, especially if they are used by multiple applications. Version control and thorough testing are crucial.

Parameters

Stored procedures can have input, output, and input/output parameters. This allows for flexible data exchange between the procedure and the calling environment.


CREATE PROCEDURE usp_AddEmployee
    @FirstName VARCHAR(50),
    @LastName VARCHAR(50),
    @Department VARCHAR(50),
    @Salary DECIMAL(10, 2),
    @NewEmployeeID INT OUTPUT -- Output parameter
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO Employees (FirstName, LastName, Department, Salary)
    VALUES (@FirstName, @LastName, @Department, @Salary);

    SET @NewEmployeeID = SCOPE_IDENTITY(); -- Get the ID of the newly inserted row
END;

To execute a procedure with an output parameter:


DECLARE @GeneratedID INT;
EXEC usp_AddEmployee
    @FirstName = 'Jane',
    @LastName = 'Doe',
    @Department = 'Sales',
    @Salary = 50000.00,
    @NewEmployeeID = @GeneratedID OUTPUT;

SELECT @GeneratedID AS NewlyAddedEmployeeID;

Security Considerations:

Be cautious when using dynamic SQL within stored procedures. Sanitize all inputs to prevent SQL injection vulnerabilities.

Microsoft Docs