Calling a stored proc within a stored proc

Question

Hi, I am attemting to create a storedproc that reads like:

Select 
   ep.EmployeeID, GetEmployeeFirstName(ep.EmployeeID), 
   GetEmployeeLastName(ep.EmployeeID), ed.EmployeeDateOfBirth, 
   ed.EmployeeAddress, ed.EmployeeAddress2, ed.City, ed.State, ed.ZipCode
From
   EmployeeProfile ep, EmployeeDetail ed
Where 
   ep.EmployeeID = ed.EmployeeID

This block of code will be a stored procedure. My issue is that GetEmployeeFirstName is a stored proc that has to be passed an EmployeeID to get the employees first and last name.

How can I call a storedproc within a stored proc.

Thanks

Mike

Answer 1

You can use EXEC or sp_executesql to execute a stored procedure from another stored procedure. (BTW, you have not specified your RDBMS).

Doesn't your table EmployeeDetail contain the employee's first and last name?

Select    
    ep.EmployeeID, ed.FirstName 
    ed.LastName, ed.EmployeeDateOfBirth,
    ed.EmployeeAddress, ed.EmployeeAddress2, 
    ed.City, ed.State, ed.ZipCode
From   
    EmployeeProfile ep
    inner join EmployeeDetail ed ON ep.EmployeeID = ed.EmployeeID

Answer 2

These would probably be better suited as a function.

GetEmployeeFirstName(ep.EmployeeID), GetEmployeeLastName(ep.EmployeeID)

Better yet, just join the table that has the names.

Answer 3

In SQL Server, in order to call the GetEmployeeLastName within the Select statement list I would convert it to a database Function.

Answer 4

I don't understand what these stored procedures do. Even if the first and last name are not in the EmployeeProfile table, and even if you have to do some manipulation of the strings before they are returned, a join would be a much better solution than a stored procedure or function. Especially when you take performance into account.

If you have the GetEmployeexName sprocs because you use them elsewhere, that's fine. Whatever they do, I would not consider it code duplication if they don't get called from your query.

You need to understand that for every row in your result set, two other procedures or functions get called. This is extremly costly and can render an application unacceptably slow, even for relatively small result sets of a few thousand employees. I know what I am talking about - I removed a lot of function calls from queries during a recent database tuning initiative.