Sohail xIN3N Sohail xIN3N - 7 months ago 502
SQL Question

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

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