在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屋！