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

Question

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

An expression of non-boolean type specified in a context where a condition is expected, near '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

Athirst

Answer 1

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...)

Russian

Answer 2

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.

Telegraphese

I just got it because I had the WHERE clause twice... duh. – Chophouse

note: if you have multiple joins and you forgot the ON condition in any of the joins, the last join will get highlighted with the error message, but that doesn't mean the last join is the problematic one. – Cherian

Answer 3

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
                        )

Byzantine

Answer 4

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

Utter

Thank you for your response and suggestion. – Athirst

Answer 5

In my case, i got the same error, but it was because a minus sign was a hyphen. Maybe this can help someone.

Eldwun