Passing Parameters to Stored Procedures

1. Introduction

Stored procedures are precompiled collections of SQL statements that can be executed as a single unit. They can accept parameters that allow for dynamic execution based on input values. This lesson focuses on how to pass parameters to these stored procedures.

2. Types of Parameters

Stored procedures can accept the following types of parameters:

• Input Parameters: Used to pass data into the procedure.
• Output Parameters: Used to return data from the procedure.
• Input/Output Parameters: Can be used to pass data in and return data out.

3. Syntax

The general syntax for creating a stored procedure with parameters is as follows:

CREATE PROCEDURE procedure_name
    @parameter1 datatype,
    @parameter2 datatype OUTPUT
AS
BEGIN
    -- SQL statements
END
            

4. Examples

4.1 Input Parameter Example

CREATE PROCEDURE GetEmployeeByID
    @EmployeeID INT
AS
BEGIN
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END
            

4.2 Output Parameter Example

CREATE PROCEDURE GetEmployeeCount
    @Count INT OUTPUT
AS
BEGIN
    SELECT @Count = COUNT(*) FROM Employees;
END
            

4.3 Input/Output Parameter Example

CREATE PROCEDURE UpdateEmployeeSalary
    @EmployeeID INT,
    @Salary DECIMAL(10, 2) OUTPUT
AS
BEGIN
    UPDATE Employees SET Salary = @Salary WHERE EmployeeID = @EmployeeID;
END
            

5. Best Practices

When passing parameters to stored procedures, consider the following best practices:

• Always define parameters with clear and meaningful names.
• Use appropriate data types for parameters to ensure data integrity.
• Limit the number of parameters; try to keep it to a minimum.
• Document the purpose of each parameter within the stored procedure.

6. FAQ