Why I can not use a stored procedure in OUTER APPLY
block?
I need to get int value from the stored procedure dbo.GetTeacherId
and use this in WHERE
clause. Here my code:
USE [StudentsDb]
DECLARE @teacherIdOut int;
SELECT StudentLastName, StudentFirstName, StudentMiddleName, LessonName, Score, TLastName, TFirstName, TMiddleName
FROM Scores
JOIN Students
ON Scores.StudentId=Students.StudentId
JOIN Lessons
ON Scores.LessonId=Lessons.LessonId
OUTER APPLY
(
EXECUTE dbo.GetTeacherId 0, 0, @teacherId=@teacherIdOut -- here I get error
SELECT Teachers.TeacherLastName, Teachers.TeacherFirstName, Teachers.TeacherMiddleName
FROM Teachers
WHERE Teachers.TeacherId=@teacherIdOut
)T(TLastName, TFirstName, TMiddleName)
WHERE Score <=3
And is there any other way to get the value from the stored procedure?
Here my stored procedure dbo.GetTeacherId
:
USE [StudentsDb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetTeacherId] @lessonId int, @groupId int, @teacherId int OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT @teacherId=GroupTeachers.TeacherId
FROM GroupTeachers
WHERE GroupTeachers.LessonId=@lessonId AND GroupTeachers.GroupId=@groupId
END
Stored procedure is not designed for that kind of usage, as it can perform operations other then selecting data, it can work without returning data or it can return different set in different scenarios.
Unlike stored procedures, functions are exactly suited to be used inline with other queries.
You have two options:
A) Create a scalar function that will return only a
TeacherID
and use it in yourWHERE
B) Create table-valued function that can get you all the data needed and you can just join (apply) on it.
Here is some reading: Difference between Stored Procedure and Function in SQL Server