CODEWITHSUNDEEP
sqlsql-server
Moha the almighty camel
Moha the almighty camel

Reputation: 4443

sorting a table according to the result of a stored procedure

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

Upvotes: 1

Views: 43

Answers (2)

Szymon
Szymon

Reputation: 43023

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.

Upvotes: 0

MaxiWheat
MaxiWheat

Reputation: 6251

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

Upvotes: 1

Related Questions