SQL Server - 在预期条件的上下文中指定的非布尔类型的表达式，靠近“RETURN”

SQL Server - An expression of non-boolean type specified in a context where a condition is expected, near 'RETURN'

提问by Sohail xIN3N

Getting this error with the following query in SQL Server 2012.

在 SQL Server 2012 中使用以下查询获取此错误。

An expression of non-boolean type specified in a context where a condition is expected, near 'RETURN'.

在预期条件的上下文中指定的非布尔类型的表达式，靠近 'RETURN'。

CREATE FUNCTION [dbo].[GetPMResources](@UserResourceNo nvarchar(250))

   RETURNS @Resources TABLE (
   ResourceNo nvarchar(250) COLLATE Latin1_General_CS_AS not null,
   Name nvarchar(250) COLLATE Latin1_General_CS_AS not null
   ) 
  AS
  BEGIN

        Declare @RoleID int, @UserDivision nvarchar(20)
        SELECT TOP(1) @RoleID = r.ReportingRole, @UserDivision = r.DivisionCode
        FROM Resource r 
        WHERE r.ResourceNo = @UserResourceNo



        INSERT @Resources
        SELECT r.ResourceNo,Name = r.ResourceNo + ' ' + r.Name
        FROM Resource r WHERE r.ResourceNo IN
                        ( 
                            SELECT m.ResourceNo FROM JobMember m
                            JOIN Job j ON j.JobNo = m.JobNo
                            WHERE j.ProjectManagerNo = @UserResourceNo 
                            OR
                            j.AlternateProjectManagerNo = @UserResourceNo
                        ) 
                        OR
                        (
                            SELECT m.ResourceNo FROM JobMember m
                            JOIN JobTask t ON t.JobTaskNo = m.JobTaskNo
                            WHERE t.TaskManagerNo = @UserResourceNo
                            OR
                            t.AlternateTaskManagerNo = @UserResourceNo
                        )                   
        RETURN 
  END

采纳答案by Remus Rusanu

That is invalid syntax. You are mixing relational expressions with scalar operators (OR). Specifically you cannot combine expr IN (select ...) OR (select ...). You probably want expr IN (select ...) OR expr IN (select ...). Using union would also work: expr IN (select... UNION select...)

那是无效的语法。您将关系表达式与标量运算符 ( OR)混合使用。具体来说，你不能结合expr IN (select ...) OR (select ...). 你可能想要expr IN (select ...) OR expr IN (select ...). 使用 union 也可以：expr IN (select... UNION select...)

回答by Lori Kent

An expression of non-boolean type specified in a context where a condition is expected

在预期条件的上下文中指定的非布尔类型的表达式

I also got this error when I forgot to add ON condition when specifying my join clause.

当我在指定 join 子句时忘记添加 ON 条件时，我也遇到了这个错误。

回答by Rahul Garg

Your problem might be here:

你的问题可能在这里：

OR
                        (
                            SELECT m.ResourceNo FROM JobMember m
                            JOIN JobTask t ON t.JobTaskNo = m.JobTaskNo
                            WHERE t.TaskManagerNo = @UserResourceNo
                            OR
                            t.AlternateTaskManagerNo = @UserResourceNo
                        )

try changing to

尝试更改为

OR r.ResourceNo IN
                        (
                            SELECT m.ResourceNo FROM JobMember m
                            JOIN JobTask t ON t.JobTaskNo = m.JobTaskNo
                            WHERE t.TaskManagerNo = @UserResourceNo
                            OR
                            t.AlternateTaskManagerNo = @UserResourceNo
                        )

回答by Jade

YOu can also rewrite it like this

你也可以这样改写

FROM Resource r WHERE r.ResourceNo IN
        ( 
            SELECT m.ResourceNo FROM JobMember m
            JOIN Job j ON j.JobNo = m.JobNo
            WHERE j.ProjectManagerNo = @UserResourceNo 
            OR
            j.AlternateProjectManagerNo = @UserResourceNo

            Union All

            SELECT m.ResourceNo FROM JobMember m
            JOIN JobTask t ON t.JobTaskNo = m.JobTaskNo
            WHERE t.TaskManagerNo = @UserResourceNo
            OR
            t.AlternateTaskManagerNo = @UserResourceNo

        )

Also a return table is expected in your RETURN statement

在您的 RETURN 语句中还需要一个返回表