Stored Procedure Practice Example

Practice Scenario

In this practice example, you will create a stored procedure that manages employee records in a company database. The goal is to insert a new employee record into the Employees table and return the newly created employee's details.

Creating the Employees Table

Before creating the stored procedure, ensure that you have the Employees table in your database. You can create the table using the following SQL command:

CREATE TABLE Employees (
            EmployeeID INT PRIMARY KEY IDENTITY,
            FirstName VARCHAR(50),
            LastName VARCHAR(50),
            Position VARCHAR(50),
            Salary DECIMAL(10, 2)
        );

Creating the Stored Procedure

Next, create a stored procedure to insert a new employee:

CREATE PROCEDURE AddNewEmployee
        @FirstName VARCHAR(50),
        @LastName VARCHAR(50),
        @Position VARCHAR(50),
        @Salary DECIMAL(10, 2)
        AS
        BEGIN
            INSERT INTO Employees (FirstName, LastName, Position, Salary)
            VALUES (@FirstName, @LastName, @Position, @Salary);

            SELECT * FROM Employees WHERE EmployeeID = SCOPE_IDENTITY();
        END;

Executing the Stored Procedure

You can execute the stored procedure to add a new employee. For example:

EXEC AddNewEmployee @FirstName = 'Khizar', @LastName = 'Mirza', @Position = 'Software Engineer', @Salary = 75000.00;

This command will insert a new employee record and return the details of the newly created employee.

Practice Exercise

Now it's your turn! Try creating your own stored procedure to update an employee's salary. Here’s a hint to get you started:

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