sorting a table according to the result of a stored procedure

1
votes

I have a table of employees and I want to get the top three employees of the month.
I have a stored procedure that takes the employee ID and and returns a number indicating that employee quality of work.

I was hoping to find something like:

SELECT top (3)  name
FROM employee
order by sp_emplyeePerformance employee.ID -- of course this doesn't work

is such thing possible ? or should I create a temporary table with the employee name and performance and then query the temp table

2
sqlsql-server
You need to go the temp table route. – Gordon Linoff

2 Answers

1
votes

You might be looking for a User Defined Function instead of a Stored Proc. That UDF would accept the employee's ID as a parameter and return a value (the employee's score), so you could sort by it.

Example :

CREATE FUNCTION dbo.fnEmployee_score (@employeeId INT)
RETURNS INT
AS
BEGIN
    -- Your logic
    RETURN @score;
END
0
votes

You cannot execute a stored procedure from a SELECT query.

Your options are:

  • Use a temporary table to store each employee Id, execute a stored procedure for each employee and then query the temporary table.

  • Turn your stored procedure into a function.

  • Change your store procedure to do the calculations on the whole employee in one go.