CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Sohail xIN3N
Sohail xIN3N

Reputation: 3031

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

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

Upvotes: 22

Views: 319653

Answers (5)

César León
César León

Reputation: 3050

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

Upvotes: 1

Jade
Jade

Reputation: 2992

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

Upvotes: 2

Lori Kent
Lori Kent

Reputation: 349

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.

Upvotes: 23

Rahul Garg
Rahul Garg

Reputation: 378

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
                        )

Upvotes: 6

Remus Rusanu
Remus Rusanu

Reputation: 294207

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

Upvotes: 20

Related Questions