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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2012