6.1 Creating and Executing Stored Procedures

Stored procedures are precompiled sets of one or more SQL statements that can be executed as a single unit. They are stored in the database and can be invoked with a single command. Here's how you can create and execute stored procedures in SQL Server.

1. Creating a Simple Stored Procedure

Let's start by creating a basic stored procedure that selects data from a table.

-- Step 1: Create a simple stored procedure
CREATE PROCEDURE GetEmployeeData
AS
BEGIN
    SELECT EmployeeID, FirstName, LastName, Department
    FROM Employees;
END;

2. Executing the Stored Procedure

Now, let's execute the stored procedure we just created.

-- Step 2: Execute the stored procedure
EXEC GetEmployeeData;

The output of the stored procedure will be displayed in a table format. Here's a sample representation of the output:

EmployeeID	FirstName	LastName	Department
1	John	Doe	IT
2	Jane	Smith	HR
3	Bob	Johnson	Finance

3. Adding Parameters to a Stored Procedure

Stored procedures can accept parameters. Let's create a stored procedure that takes a department name as a parameter and returns employees from that department.

-- Step 3: Create a stored procedure with parameters
CREATE PROCEDURE GetEmployeesByDepartment
    @DeptName NVARCHAR(50)
AS
BEGIN
    SELECT EmployeeID, FirstName, LastName
    FROM Employees
    WHERE Department = @DeptName;
END;

4. Executing the Parameterized Stored Procedure

Now, execute the parameterized stored procedure with a specific department name.

-- Step 4: Execute the parameterized stored procedure
EXEC GetEmployeesByDepartment @DeptName = 'IT';

The output will be displayed in a table format, showing only the employees from the 'IT' department.

EmployeeID	FirstName	LastName
1	John	Doe
4	Sarah	White

This tutorial covers the basics of creating and executing stored procedures in SQL Server, including simple procedures and those with parameters. Feel free to explore more advanced features and options based on your specific requirements.