Transact-SQL Stored Procedures

Stored procedures are a set of one or more Transact-SQL statements that are compiled and stored on the server. They can be called by applications, other stored procedures, or ad hoc queries.

Stored procedures offer several benefits:

• Performance: Procedures are compiled once and stored, reducing network traffic and execution overhead for repeated calls.
• Reusability: A single procedure can be used by multiple applications or users.
• Maintainability: Changes to business logic can be made in one place (the procedure) without affecting the client applications.
• Security: Permissions can be granted to execute procedures without granting direct access to underlying tables.
• Encapsulation: Complex logic is hidden from users, simplifying database access.

The basic syntax for creating a stored procedure is:

CREATE PROCEDURE schema_name.procedure_name
    [ { @parameter [ type ] [ = default ] [ OUTPUT ] }
        [ ,...n ]
    ]
AS
BEGIN
    -- Transact-SQL statements
    SELECT 'Hello, World!';
END;
GO

Parameters

Stored procedures can accept input parameters and return output parameters. Parameters are declared with an '@' prefix and have a specified data type. An optional default value can be provided, and the OUTPUT keyword indicates that the parameter is for returning a value.

CREATE PROCEDURE usp_GetEmployeeName
    @EmployeeID INT,
    @EmployeeName NVARCHAR(100) OUTPUT
AS
BEGIN
    SELECT @EmployeeName = Name
    FROM Employees
    WHERE EmployeeID = @EmployeeID;
END;
GO

Stored procedures are executed using the EXECUTE or EXEC statement.

-- Execute a procedure with no parameters
EXEC usp_GetCustomerOrders;

-- Execute a procedure with input parameters
EXEC usp_GetEmployeeName @EmployeeID = 101;

-- Execute a procedure with input and output parameters
DECLARE @EmpName NVARCHAR(100);
EXEC usp_GetEmployeeName @EmployeeID = 102, @EmployeeName = @EmpName OUTPUT;
PRINT @EmpName;
GO

You can modify an existing stored procedure using ALTER PROCEDURE. To remove a stored procedure, use DROP PROCEDURE.

-- Modify an existing procedure
ALTER PROCEDURE usp_GetEmployeeName
    @EmployeeID INT,
    @EmployeeName NVARCHAR(100) OUTPUT
AS
BEGIN
    SELECT Name
    FROM Employees
    WHERE EmployeeID = @EmployeeID;
END;
GO

-- Drop a procedure
DROP PROCEDURE usp_GetEmployeeName;
GO