Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


SSRS problem with mutli-value param and "expression of non-boolean type specified ..."

Posted on 2013-05-18
Medium Priority
Last Modified: 2013-05-21

I'm getting an error of An expression of non-boolean type specified in a context where a condition is expected, near ','.

It's obviously to do with the multi-value param but I don't get why ?? it works with a single value but not multi ? Is it because the contractnumber is defined as int on the db ?

Here's the sql...

SELECT Count (job.scheduleddate) AS scheduled,
       Count (job.datecompleted) AS completed
FROM   job
       INNER JOIN jobline
               ON job.id = jobline.jobid
       INNER JOIN site
               ON job.siteid = site.id
       INNER JOIN contract
               ON site.contractid = contract.id
       INNER JOIN lift
               ON jobline.liftid = lift.id
       INNER JOIN company
               ON contract.companyid = company.id
       INNER JOIN systemuser
               ON job.engineerid = systemuser.id
WHERE  ( job.lookupjobtypeid = 9 )
       AND ( job.lookupjobstatusid <> 7 )
       AND ( job.scheduleddate BETWEEN @pStartDate AND Dateadd(mi, 1439,
                                                       @pEndDate) )
       AND ( lift.contractstatus = 1 )
       AND ( jobline.lookupstatusid < 4 )
       AND ( @pContract = -1
              OR contract.contractnumber IN ( @pContract ) )
       AND ( @pCompany = -1
              OR company.id IN ( @pCompany ) )
Question by:stevejebson
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
LVL 49

Expert Comment

ID: 39177227
there is no obvious syntax error in the above, as you say it has to be the values help by a parameter, without knowing what you are placing in there it's impossible to be exact. However it is probably only going to be @pContract of @pCompany as these are intended to hold comma seperated lists. With contractnumber being an int you will not be able to simply send it a string containing what looks like a set of integers

'1,2,3,4,5' is still a string
It may be working as a single value because sql server is doing an implicit conversion to int for you; '1' could be converted to an int '1,2,3' could not. Hence the conversion is failing and the error message is indicating it cannot deal with any commas in that parameter.

you would have to either:
a. dynamically generated sql so the string is treated as integers when executed (not great)
b. convert contractnumber to varchar (also not terrific but better than a)
c. insert the wanted contractnumbers into a temp table? {+ edit or CTE which could be easier}

b. would be the easiest to implement but would adversely impact query efficiency - this impact may be acceptable - cant tell

d. ? a longer shot might be a function that returns a table if integers from a comma separated parameter (I've never done this, so its a maybe)

Author Comment

ID: 39177539

thanks for reply, apologies for late response (trip to Argos, bbq now built!), I agree with your analysis...it all works fine with single value in pContract (we'll ignore pCompany cos its the same thing, just a diff field). I must warn you though i'm pretty amateur when it somes to sql, and ssrs for that matter !...

b. if I understand you correctly this would be to change the definition on the Contract table to hold ContractNumber as varchar ? I can't change this, not my table and would be hard to justify.

c. sounds interesting, if I insert params into temp table would others running the rpt also pick these up ?? would I then need to delete them ? maybe clear it out before I insert ?....thinking while Im typing, could start to get a bit random !

if you could just expand a little on c. i'd like to give that a go...temp table or table var ? because this report is linked to 6 others via linked report i'd like to keep the temp data available across reports but not users if that makes sense ?


LVL 49

Expert Comment

ID: 39177813
b. if I understand you correctly this would be to change the definition on the Contract table to hold ContractNumber as varchar ?
that's not what I had in mind, something a little easier (but not highly desirable)

       AND ( @pContract = -1
              OR cast(contract.contractnumber as varchar) IN ( @pContract ) )

I suggest you try this, it may not be too bad It would prove if the problem is those delimited strings. If the performance of this approach is acceptable then you may want to stick to it. The downside here is it would probably stop the use of the index on that field - which is why I say its not desirable - but many queries have to do similar things.

c. temp table could be tricky, yes it would need to be cleared, and it could be a problem for concurrent use. I mention it because it exists as an option, not sure I'd adopt it necessarily. A table var from a function might be the better variant. There are several scripts around to break-up delimited strings into pieces, it would be a small variation on one of these I think.

Using a recursive CTE might also work although not quite sure how to write that code.
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.


Author Comment

ID: 39179488

sadly the cast(contractnumber as varchar) didn't work,  it still produced the same error

I've been trying to look at cte's....jeez, they're not so easy to understand.

i'm still a bit suprised that having a multi value integer as a parameter and not being able to do the simple where id in (@parameter) doesn't work ! maybe that's just my frustration coming through !

maybe the table var from function would be the easiest for me to research and find out how to do ?....

LVL 49

Expert Comment

ID: 39179759
mmmm, if that cast to varchar didn't work, and before attempting  more complexity, we have to consider that the analysis of the problem isn't correct. Precisely how are you forming the parameters? Perhaps you could inspect the actual values being passed this way?
SELECT Count(job.scheduleddate) AS scheduled
	, Count(job.datecompleted) AS completed
    , @pContract as pCntrct
    , @pCompany as pCmpy
FROM job
INNER JOIN jobline ON job.id = jobline.jobid
INNER JOIN site ON job.siteid = site.id
INNER JOIN contract ON site.contractid = contract.id
INNER JOIN lift ON jobline.liftid = lift.id
INNER JOIN company ON contract.companyid = company.id
INNER JOIN systemuser ON job.engineerid = systemuser.id
WHERE (job.lookupjobtypeid = 9)
	AND (job.lookupjobstatusid <> 7)
	AND (
		job.scheduleddate BETWEEN @pStartDate
			AND Dateadd(mi, 1439, @pEndDate)
	AND (lift.contractstatus = 1)
	AND (jobline.lookupstatusid < 4)

Open in new window

LVL 49

Expert Comment

ID: 39179811
ok, thinking about this again, the error message you are getting (in initial question) may be due to a leading comma in your parameter

but even if that leading comma is removed, the IN(@parameter) isn't going to work
and it isn't going to work even with cast(contractnumber to varchar)
(sorry, not a good suggestion)

please see this: http://sqlfiddle.com/#!3/1fa93/6934

Maybe others are able to offer a solution here
LVL 49

Expert Comment

ID: 39180209
The following approach uses 2 CTE's  to split your parameters using an XML based approach. This assumes those parameters will only every carry digits and the delimiter (comma). i.e. if you put bad characters into the parameters the query would fail and don't use a delimiter that would "disturb XML" like a > or < (not that you are likely to).

You should also take care that your parameters should not start with a comma.

The third CTE here is purely so I could test easily, you would use real tables instead of this.
DECLARE @pContract varchar(max)
DECLARE @pCompany varchar(max)
declare @Splitter char(1)
SET @Splitter = ','

SET @pContract = '2,3,4,5'
SET @pCompany = '22,33,44'

SplitContracts AS(
  SELECT CAST('<v>' + REPLACE(@pContract, @Splitter, '</v><v>') + '</v>' AS XML) AS Split
SplitCompanies AS(
  SELECT CAST('<v>' + REPLACE(@pCompany, @Splitter, '</v><v>') + '</v>' AS XML) AS Split
contract as (
  select 1 as contractnumber, 11 as company_id union all
  select 2 as contractnumber, 22 as company_id union all
  select 3 as contractnumber, 33 as company_id union all
  select 4 as contractnumber, 44 as company_id union all
  select 5 as contractnumber, 55 as company_id union all
  select 6 as contractnumber, 66 as company_id union all
  select 7 as contractnumber, 77 as company_id union all
  select 8 as contractnumber, 88 as company_id union all
  select 9 as contractnumber, 99
from contract
where (@pContract = '-1'
       contractnumber IN (
                            SELECT cast(x.v.value('.', 'bigint') as bigint) AS Value
                            FROM SplitContracts
                            CROSS APPLY Split.nodes('//v') x(v)
AND   (@pCompany = '-1'
       company_id IN (
                            SELECT cast(x.v.value('.', 'bigint') as bigint) AS Value
                            FROM SplitCompanies
                            CROSS APPLY Split.nodes('//v') x(v)

Open in new window

also note that minus one in the above has changed to a string
LVL 49

Expert Comment

ID: 39180221
I think its also worth mentioning that this xml based approach is also based on the assumption you are not selecting thousands of contracts and/or companies, instead I hope you are likely to be selecting a relatively small number of these. Please test against the largest likely selections of these to ensure performance is acceptable.

These are useful references if you need more information or want to explore this parameter issue more deeply:

an often cited reference on this split string topic:

useful introduction to the background of this issue, and some alternative methods for handling it

Author Comment

ID: 39182160
Hey PortletPaul,

First off, thankyou so much for putting so much effort into this, I really appreciate it, especially as it has been such a frustrating experience. I don't know about you but something doesn't feel quite right about this, so I decided to go back and start again and I've now identified what I think to be the problem although i'm not sure why !

It's to do with the @pContract = -1  

and ( @pContract = -1 or c.ContractNumber in (@pContract ) )    doesn't work
and ( c.ContractNumber in (@pContract ) ) does work

any thoughts ??

LVL 49

Accepted Solution

PortletPaul earned 2000 total points
ID: 39182883
also note that minus one in the above has changed to a string
I mentioned this a while ago :)

and ( @pContract = '-1' --<< there needs to be quotes use here
or c.ContractNumber in (@pContract ) )

Author Comment

ID: 39184136
It's all in the detail !!   I completely missed it, thanks so much, really top notch support there, thanks again :D


Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question