Stored Procedure Parameters in SQL Server

Stored Procedure Parameters in SQL Server

Introduction:
In SQL Server, a stored procedure is a pre-compiled collection of one or more SQL statements that are stored in the database and can be executed using a single command. Stored procedures can accept parameters, which are placeholders for values that can be passed into the procedure when it is called. Using parameters in stored procedures enhances reusability and flexibility.

Creating a Stored Procedure with Parameters:

To create a stored procedure with parameters, you need to define the parameters in the procedure declaration. The syntax for creating a stored procedure with parameters is as follows:

sql
CREATE PROCEDURE [schema_name].[procedure_name]
    @parameter1 data_type,
    @parameter2 data_type,
    -- add more parameters as needed
AS
BEGIN
    -- SQL statements using the parameters
END

Example:

Let's create a simple stored procedure named "GetEmployeeInfo" that takes an employee ID as a parameter and retrieves the employee's information from an "Employees" table.

sql
CREATE PROCEDURE dbo.GetEmployeeInfo
    @EmployeeID INT
AS
BEGIN
    SELECT EmployeeID, FirstName, LastName, Department
    FROM Employees
    WHERE EmployeeID = @EmployeeID;
END

Explanation:

1. We use the CREATE PROCEDURE statement to create the stored procedure. The dbo before the procedure name specifies the schema in which the procedure will be created. You can replace dbo with the desired schema name.

2. The @EmployeeID INT line declares a parameter named @EmployeeID with the data type INT.

3. The AS keyword is used to begin the body of the stored procedure.

4. Inside the procedure body, we use the SELECT statement to retrieve employee information from the "Employees" table based on the provided @EmployeeID.

Executing the Stored Procedure:

To execute the stored procedure, you can use the EXEC or EXECUTE statement:

sql
EXEC dbo.GetEmployeeInfo @EmployeeID = 123;

In this example, the stored procedure GetEmployeeInfo is called with the @EmployeeID parameter set to 123. The stored procedure will return the information of the employee with ID 123.

Conclusion:

Stored procedure parameters in SQL Server allow you to pass values to the procedure, making them dynamic and reusable. By using parameters, you can create powerful and flexible database routines to meet various business needs.