SQL Server : alter stored procedure to accept parameters
<p><code>Employee</code> table</p>

<p><a href="https://i.stack.imgur.com/ju47e.png" rel="nofollow"><img src="https://i.stack.imgur.com/ju47e.png" alt="Employee Table"></a></p>

<p>Write a SQL statement that would alter the below-mentioned pre-existing stored procedure to accomplish the following:</p>

<ul>
<li>allow for a parameter to be supplied containing a manager's <code>Emp_ID</code></li>
<li>use the supplied parameter to qualify the <code>Emp_ID</code> in the query</li>
<li>assign the parameter a value of zero if no parameter value is supplied</li>
<li>rewrite the result statement as a <code>JOIN</code> that includes the Manager's last name</li>
<li><p>ensure the results are displayed as follows:</p>

<pre><code>Manager's Last Name
Employee's Last Name
Employees Hire Date
</code></pre></li>
<li><p>order the results of the query by the Manager's last name, the most recent hires and the Employee's last name</p></li>
</ul>

<p>This is the pre-existing stored procedure code:</p>

<pre><code>CREATE PROCEDURE dbo.sp_GetEmployeeNm 
AS
   SELECT  
       Last_Nm, Hire_Dt
   FROM 
       dbo.Employee
   WHERE 
       Mgr_ID IN (SELECT Emp_ID 
                  FROM dbo.Employee 
                  WHERE Emp_ID = 12345)
</code></pre>

<p>Is there anyone who knows how to correctly ALTER this stored procedure? </p>

<p>This is what I tried:</p>

<pre><code>ALTER PROCEDURE dbo.sp_GetEmployeeNm (@Emp_ID INT = 0) 
AS 
BEGIN
    SELECT 
        m.Last_Nm AS Manager's Last Name, 
        e.Last_Nm AS Employee's Last Name, 
        e.Hire_Dt AS Employee's Hire Date
    FROM
        Employee  e
    INNER JOIN 
        Employee m ON e.Emp_ID = m.Mgr_ID
    WHERE 
        e.Emp_ID = 12345
    ORDER BY 
        (m.Last_Nm, e.Hire_Dt, e.Last_Nm) DESC
END
</code></pre>