Scribd
Upload
22 views4 pages

Section 5 Lab

The document outlines a series of SQL stored procedures for managing employee records in a database. Procedures include inserting employee data, checking salaries, updating names, selecting employees by department, deleting records in a range, and counting employees per department. Additional procedures allow for optional parameters during insertion and retrieving specific employee details based on their ID.

Uploaded by

komeha089
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
22 views4 pages

Section 5 Lab

The document outlines a series of SQL stored procedures for managing employee records in a database. Procedures include inserting employee data, checking salaries, updating names, selecting employees by department, deleting records in a range, and counting employees per department. Additional procedures allow for optional parameters during insertion and retrieving specific employee details based on their ID.

Uploaded by

komeha089
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

-- 1.

Create a procedure that can insert full record in employee

CREATE PROCEDURE InsertEmployee

@id INT, @name NVARCHAR(100), @address NVARCHAR(255), @salary MONEY, @department


NVARCHAR(100)

AS BEGIN

INSERT INTO Employee (ID, Name, Address, Salary, Department)

VALUES (@id, @name, @address, @salary, @department);

END;

-- 2. Create a procedure to check salary and print result

CREATE PROCEDURE CheckSalary @emp_id INT

AS BEGIN

DECLARE @salary MONEY;

SELECT @salary = Salary FROM Employee WHERE ID = @emp_id;

IF @salary >= 2500

PRINT '>=2500';

ELSE

PRINT '<2500';

END;

-- 3. Update employee name given its ID

CREATE PROCEDURE UpdateEmployeeName

@emp_id INT, @new_name NVARCHAR(100)

AS BEGIN

UPDATE Employee

SET Name = @new_name

WHERE ID = @emp_id;

END;
-- 4. Select employee data given its department

CREATE PROCEDURE GetEmployeesByDepartment

@department NVARCHAR(100)

AS

BEGIN

SELECT * FROM Employee WHERE Department = @department;

END;

-- 5. Using loop, delete records from ID 1 to 10

CREATE PROCEDURE DeleteEmployeesInRange

AS

BEGIN

DECLARE @id INT = 1;

WHILE @id <= 10

BEGIN

DELETE FROM Employee WHERE ID = @id;

SET @id = @id + 1;

END;

END;

-- 6. Procedure to insert only ID and Name using the first procedure

CREATE PROCEDURE InsertEmployeeIDName

@id INT, @name NVARCHAR(100)

AS

BEGIN

EXEC InsertEmployee @id, @name, NULL, NULL, NULL;


END;

-- 7. Insert into Employee Table with optional parameters

CREATE PROCEDURE InsertOptionalEmployee

@id INT,

@name NVARCHAR(100) = NULL,

@address NVARCHAR(255) = NULL,

@salary MONEY = NULL,

@department NVARCHAR(100) = NULL

AS

BEGIN

INSERT INTO Employee (ID, Name, Address, Salary, Department)

VALUES (@id, @name, @address, @salary, @department);

END;

-- 8. Procedure that returns number of employees per department

CREATE PROCEDURE CountEmployeesByDepartment

AS

BEGIN

SELECT Department, COUNT(*) AS EmployeeCount FROM Employee

GROUP BY Department;

END;

-- 9. Procedure that takes emp id and returns its name and salary

CREATE PROCEDURE GetEmployeeDetails

@emp_id INT, @name NVARCHAR(100) OUTPUT, @salary MONEY OUTPUT

AS
BEGIN

SELECT @name = Name, @salary = Salary FROM Employee WHERE ID = @emp_id;

END;

You might also like