title: SQL Server : alter stored procedure to accept parameters
body: <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>