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

0
votes

When I run the below query on SQL Server it is working fine with single value or multiple values. But When I try to execute the same on SSRS Dataset it is throwing an error for multiple values.

ERROR: SSRS: An expression of non boolean type specified in a context where a condition is expected near ','. Incorrect syntax near the keyword 'ELSE'.

It is working fine with single value. Not sure what is the issue or is there any other way that we can write this query.

DECLARE @UserTeam VARCHAR(max) = 'Team1,Team2,Team3,Team4,Team5,Team6,'''

-- Values can inculdes null. if user select blank and Team1,Team2 we need to pull data which is null, Team1, Team2 
IF Object_id('tempdb.dbo.#Team', 'U') IS NOT NULL
  DROP TABLE #team;

SELECT item AS Team
INTO   #team
FROM   dbo.FNSPLIT(@UserTeam, ',')

IF @UserTeam = ''
  BEGIN
      SELECT U.username,
             T.teamcode
      FROM   [User].vwuser u
             LEFT JOIN[User].userteam UT
                    ON U.userid = UT.userid
             LEFT JOIN [User].team AS T
                    ON T.teamid = Ut.teamid
      WHERE  T.teamcode IS NULL
      ORDER  BY U.username
  END
ELSE
  BEGIN
      SELECT U.username,
             T.teamcode
      FROM   [User].vwuser u
             LEFT JOIN [User].userteam UT
                    ON U.userid = UT.userid
             LEFT JOIN [User].team AS T
                    ON T.teamid = Ut.teamid
             JOIN #team tm
               ON T.teamcode = tm.team
      WHERE  ( U.username NOT LIKE '%System%' )
      ORDER  BY U.username
  END
2
sqlsql-serverreporting-servicessql-server-2012ssrs-2012

2 Answers

0
votes

You can execute the next sentence, to test the function fnSplit:

DECLARE @UserTeam VARCHAR(MAX) = 'Team1,Team2,Team3,Team4,Team5,Team6,'''

SELECT item AS Team FROM dbo.fnSplit(@UserTeam,',')

If the above function is working correctly you will see seven rows:

  • Team1
  • Team2
  • Team3
  • Team4
  • Team5
  • Team6
  • '(Single quote)
0
votes

I could figured out what is the issue. It's because UserTeam parameter is not accepting multiple selections. To resolve I added below expression. 

=JOIN(Parameters!UserTeam.Value,",")

Dataset Properties -> parameters -> @UserTeam -> add expression. Basically it will delimit in a comma separated values and function split dbo.fnSplit(@UserTeam, ',') will convert into a table.

Also I have changed a query in a better way without having IF condition

SELECT U.userName, T.teamCode 
FROM [User].vwUser u
   left join [User].UserTeam UT  ON U.userId = UT.userId 
   left join [User].Team AS T ON T.teamId = Ut.teamId 
WHERE        
   U.userName NOT LIKE '%System%'
   AND ISNULL(T.teamCode,'')  IN (SELECT ISNULL(item,'') FROM dbo.fnSplit(@UserTeam,','))
ORDER BY U.userName