在 OUTER APPLY 块中执行存储过程 [英] Execute stored procedure in OUTER APPLY block

问题描述

为什么我不能在 OUTER APPLY 块中使用存储过程?我需要从存储过程 dbo.GetTeacherId 获取 int 值并在 WHERE 子句中使用它.这是我的代码:

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

还有其他方法可以从存储过程中获取值吗?这里是我的存储过程 dbo.GetTeacherId:

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.

您有两个选择:

A) 创建一个仅返回 TeacherID 的标量函数，并在您的 WHERE

A) Create a scalar function that will return only a TeacherID and use it in your WHERE

CREATE FUNCTION udfGetTeacherID
(
    @lessonId int, @groupId int
)
RETURNS int
AS
BEGIN

    DECLARE @teacherId INT;

    SELECT @teacherId = GroupTeachers.TeacherId
    FROM GroupTeachers
    WHERE GroupTeachers.LessonId=@lessonId AND GroupTeachers.GroupId=@groupId;


    RETURN @teacherId;

END
GO

B) 创建表值函数，该函数可以为您提供所需的所有数据，您只需加入(应用)即可.

B) Create table-valued function that can get you all the data needed and you can just join (apply) on it.

CREATE FUNCTION udfGetTeacherName
(
    @lessonId int, @groupId int
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT t.TeacherLastName, t.TeacherFirstName, t.TeacherMiddleName
    FROM Teachers t
    INNER JOIN GroupTeachers g ON  T.TeacherID = g.TeacherID
    WHERE g.LessonId=@lessonId AND g.GroupId=@groupId
)
GO

这里有一些阅读:区别SQL Server 中存储过程和函数之间的关系

这篇关于在 OUTER APPLY 块中执行存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！