在OUTER执行存储过程APPLY块(Execute stored procedure in OUT

2019-10-18 20:19发布

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

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

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

Answer 1:

存储过程是不适合那种使用的,因为它可以执行其他操作然后选择数据,它可以在不返回数据的工作,也可以在不同情况下返回不同的组。

不同于存储过程,函数是完全适合于与其他查询在线使用。

你有两个选择:

A)创建一个标量函数将返回只有TeacherID和使用它你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)创建表值函数,可以让你需要的所有数据,您只需加入(申请)就可以了。

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存储过程和函数间的差



文章来源: Execute stored procedure in OUTER APPLY block