• Register
0 votes
189 views

Problem :

I am facing following error with the below query in SQL Server 2012.

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

 

SQL Query:

CREATE FUNCTION [dbo].[GetMYPMResources](@UserResourceNo nvarchar(250))
RETURNS @MyResources TABLE (
MyResourceNo nvarchar(250) COLLATE Latin1_General_CS_AS not null,
MyName 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.MyResourceNo = @UserResourceNo
INSERT @MyResources
SELECT r.MyResourceNo,MyName = r.MyResourceNo + ' ' + r.MyName
FROM Resource r WHERE r.MyResourceNo IN
( 
SELECT m.MyResourceNo FROM JobMember m
JOIN Job j ON j.JobNo = m.JobNo
WHERE j.ProjectManagerNo = @UserResourceNo 
OR
j.AlternateProjectManagerNo = @UserResourceNo
 ) 
 OR
 (
 SELECT m.MyResourceNo FROM JobMember m
 JOIN JobTask t ON t.JobTaskNo = m.JobTaskNo
 WHERE t.TaskManagerNo = @UserResourceNo
 OR
 t.AlternateTaskManagerNo = @UserResourceNo
 )                   
        RETURN 
  END
6 5 3
7,540 points

Please log in or register to answer this question.

1 Answer

0 votes

Solution :

Your problem might be following query snippet:

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

try updating it to as below

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

 

9 7 4
38,600 points

Related questions

0 votes
1 answer 8 views
8 views
Problem: How to select a partial of a table for exporting using SQL? How can I do that? Thanks
asked Mar 9 ummesalma 29.2k points
0 votes
2 answers 269 views
269 views
Problem : I want to access my hosting server’s database through SQL Server Management Studio, everything is fine but when I use the following command use myDatabase it gives me following error: The server principal "****" is not able to access the database "****" under the current security context. Can anyone give me solution on my problem?
asked Nov 21, 2019 peterlaw 6.9k points
0 votes
1 answer 4 views
4 views
Problem: sql server management studio export to csv.
asked Apr 11 Dan phillip 4.8k points
0 votes
1 answer 378 views
378 views
Problem : I don't understand why I am unable to insert this. I can't spot any problem. The error message is as below : Conversion failed when converting from a character string to uniqueidentifier. A GUIDs are the ... Server 2012 The columns are as below id        uniqueidentifier, myid      uniqueidentifier, friendid  uniqueidentifier, time1     datetime nullable, time2     datetime nullable
asked Jan 17, 2020 jwilliam 3.9k points
0 votes
1 answer 122 views
122 views
Problem : My Stored Procedure is giving me following error transaction count after execute indicates a mismatching number of begin and commit statements
asked Nov 15, 2019 peterlaw 6.9k points
0 votes
1 answer 32 views
32 views
Problem: system.componentmodel.win32exception: the network path was not found.
asked Feb 12 Sabbir Hossain 14.6k points
1 vote
1 answer 188 views
188 views
Problem : My task is to send email with the file attachments also this attachment must come from the image type data field. If I try to run the below code in both the query window and the stored procedure but my email was not sent and in the query window I can only see ... = 1,     @query_no_truncate = 1; I need to send email but still unable to do so. I am looking for help in fixing the issue.
asked May 6, 2020 stewart 4k points
0 votes
1 answer 15 views
15 views
Problem: I am a beginner to sql. I have the 123MB sql file which I am trying to execute on my local PC. But I am facing the below error: Cannot execute script: Insufficient memory to continue the execution of the program. Whenever I click on Ctlr+E then it ... above error. I have tried to fix the above sql error with the help of online tutorials but they were not useful in fixing the error.
asked Aug 28, 2020 Raphael Pacheco 4.9k points
1 vote
1 answer 5 views
5 views
Problem : How to do > Script file to enable sysadmin role in SQL Server 2012
asked 6 days ago Abik Dey 4k points