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