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

Posted on 2013-05-18
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 = jobline.jobid
       INNER JOIN site
               ON job.siteid =
       INNER JOIN contract
               ON site.contractid =
       INNER JOIN lift
               ON jobline.liftid =
       INNER JOIN company
               ON contract.companyid =
       INNER JOIN systemuser
               ON job.engineerid =
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 IN ( @pCompany ) )
Question by:stevejebson
  • 7
  • 4
LVL 48

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 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 48

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.
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


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 48

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 = jobline.jobid
INNER JOIN site ON job.siteid =
INNER JOIN contract ON site.contractid =
INNER JOIN lift ON jobline.liftid =
INNER JOIN company ON contract.companyid =
INNER JOIN systemuser ON job.engineerid =
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 48

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:!3/1fa93/6934

Maybe others are able to offer a solution here
LVL 48

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 48

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 48

Accepted Solution

PortletPaul earned 500 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

740 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