Question

Create a stored procedure called sp_emp_address to display the last name, first name, address, city, province, and postal code from the Employees table for a particular employee. The employee id will be an input parameter for the stored procedure. Use the following query to test your stored procedure and produce the result set listed below:

EXEC sp_emp_address 3
LastName
FirstName
Address
City
Province
PostalCode
Leverling
Janet
722 Moss Bay Blvd.
Richmond
BC
V2K 8Y7
rows affected

Answer 1

To create the stored procedure, use the provided SQL code. Use the EXEC command followed by the stored procedure name to test it. The stored procedure takes an input parameter and returns employee information.

Step-by-step explanation:

To create a stored procedure called sp_emp_address to display employee information, you can use the following SQL code:

CREATE PROCEDURE sp_emp_address
employeeId INT
AS
BEGIN
SELECT LastName, FirstName, Address, City, Province, PostalCode
FROM Employees
WHERE EmployeeId = employeeId;
END

This stored procedure takes an input parameter called employeeId and returns the last name, first name, address, city, province, and postal code of the employee with the specified ID from the Employees table.

To test the stored procedure, you can use the following query:

EXEC sp_emp_address 3;

Hence, the stored procedure takes an input parameter and returns employee information.