question

Gehima2016 avatar image
Gehima2016 asked

OUTPUT PARAMETER QUERY

--C) Build a SP that passes in EmpID and returns an out parameter of the MgrID - (Create the SP and verify it works correctly). -- (Keep in mind that the MgrID will also be an individual 's empID., since the manager are still employees) ALTER PROCEDURE Sproc_GetMgrIdUsingEmpID @EmpID int, @MgrID int OUTPUT AS BEGIN SET NOCOUNT ON; SELECT @MgrID = MgrID FROM [dbo].[Emp_List] WHERE [EmpID] = @EmpID END GO --Test the procedure DECLARE @EmppID int, @MgrrID int SET @EmppID = 2 EXEC Sproc_GetMgrIdUsingEmpID @EmpID = @EmppID, @MgrID = @MgrrID OUTPUT; SELECT @MgrrID GO --d) Start a New Query and separate it from the previous SP. Declare an empid int and manager_name varchar(50) variable CREATE PROCEDURE Sproc_GetMgrIdUsingEmpID2 @EmpID int, @MgrID int OUTPUT AS BEGIN SET NOCOUNT ON; SELECT @MgrID = MgrID FROM [dbo].[Emp_List] WHERE [EmpID] = @EmpID END GO DECLARE @Emp2ID int, @MgrrID int, @Manager_Name varchar(50) --e) HARD CODE YOUR NEW EMPID VARIABLE AND PASS IT INTO YOUR NEW SP TO RETURN THE mgrID SET @Emp2ID = 7 EXEC Sproc_GetMgrIdUsingEmpID2 @EmpID = @Emp2ID, @MgrID = @MgrrID OUTPUT; SELECT @MgrrID GO --f) Capture that mgrID in a variable and use that mgrid variable to determinbe the Managers name DECLARE @Manager_Name varchar(50), @MgrrID int --(Create another statement which locates the Manager's name by using MgrID SET @Manager_Name = (SELECT [EmpName] AS Manager_Name FROM [dbo].[Emp_List] WHERE MgrID = @MgrrID) --G) Print the Manager's name EXEC Sproc_GetMgrIdUsingEmpID2 5, @MgrID = @MgrrID OUTPUT; PRINT @MgrrID --select * from [dbo].[Emp_List]
outputparameter
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
And do you have a question?
1 Like 1 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.