Using Output Parameters - Exercise

Practice creating a SQL Server stored procedure that returns a value using an output parameter.

This exercise is worth a total of 25 points. You will receive 25 points for writing a correct Transact-SQL statement that creates a stored procedure with an output parameter, based on the instructions below.

Create (or alter) a stored procedure named dbo.usp_GetLastName that:

1. Accepts two parameters:
   • @EmployeeID as an input parameter of type int
   • @EmployeeStatus as an output parameter of type int
2. Uses a SELECT statement to retrieve the employee’s last name for the supplied @EmployeeID.
3. Sets @EmployeeStatus inside the procedure to a meaningful status value (for example, 1 if the employee row exists, or 0 if it does not).

Hint: An output parameter must be declared with the OUTPUT keyword in the procedure definition, and you must assign a value to it inside the procedure (for example, with SET).

Type or paste your Transact-SQL statement into the text box below, then click Submit to view the Results page.