ssrs error converting data type nvarchar to int

Question

I passed this stored procedure into ssrs to generate a report but kept getting error converting data type nvarchar to int, while i don't even have any parameter that's nvarchar type.

Alter proc dbo.spPullOrderRosa1
@Subentity int,
@BegShipDate date,
@EndShipDate date,
@Store varchar(150),
@State varchar(150)
as
begin

select OpOrID as OrID, OpID, concat(OrCuFirstName,' ',OrCuLastName) as CustomerName,
b.SoName as StoreName,OpPrSKU as SKU, OpQty,StLongName as StateName,
cast(OpShipDate as date) as ShipDate, 
cast(d.DeliveryDate as date) as DeliveryDate,
e.StyName as SubEntity

from csn_order..tblOrderProduct a with (nolock)
left join csn_order..tblOrder f with (nolock) on a.OpOrID = f.OrID
left join csn_order..tblStore b with (nolock) on a.OpSoID = b.SoID
left join csn_order..tblplState c with (nolock) on f.OrCuStID = c.StID
left join csn_order..tblDeliveryDate d with (nolock) on a.OpID = d.DeliveryOpID
left join csn_order.dbo.tblSubEntity e with (nolock) on b.SoStyID = e.StyID
where (OpCancelled = 0) and (b.SoID in (select SoID from csn_order..tblStore where SoName in (select * from STRING_SPLIT(@Store, ','))  )) 
and (StID in (select StID from csn_order..tblplState where StLongName in (select * from STRING_SPLIT(@State, ',')) )) 
and (StyID =  @Subentity) and (OpShipDate >= @BegShipDate and OpShipDate <= @EndShipDate)

end

What type are all your ID fields (used in joins) and OpCancelled? Any comparison of int and nvarchar value can result in such error. — Arvo
Are any of your SSRS parameters multi-select? e.g. if @Subentity is a multi-select then IDs will be passed as a comma separated string varchar(max). E.g. "12, 34, 16" — codeulike
Does the SP work OK from Management Studio, and is the error just happening in SSRS? — codeulike
State and Store are multi-select but i used string_split for them — Yiting Ma

Robert Sheahan Robert Sheahan · Accepted Answer · 2019-03-06T15:18:58

I definitely would not write it off as just random, you don't want this popping back up when certain conditions are met. Here are a few things I'd try to narrow it down

1) You use LEFT JOINs, it can leave NULL values in fields you expect to be integers (like QTY), try wrapping them in COALESCE statements in your SELECT clause

SELECT COALESCE(OpQty, 0) as OpQty, ...

2) SSRS might be guessing the data type wrong, it might think a character field is an integer if the first few values are numbers. Figure out which field is generating the error, and perhaps cast it explicitly to NVARCHAR so SSRS won't try to use it as an integer

SELECT CONVERT(nvarchar(50), OpPrSKU) as SKU, ...

3) SSRS might have added a "TOTAL" field at the end of a DETAIL group, so it's trying to do math on a field that isn't a number. This is less likely, but possible so at least worth looking for.

But the important thing is to figure out WHICH field is generating the error so you can focus your attention on it.

EDIT: If you're using SQL Server 2012 or later, you can also explicitly set the data types returned using the EXECUTE WITH RESULT SETS clause in your dataset in the report. A good example is http://www.sqlservercentral.com/blogs/sqlstudies/2016/01/14/what-is-result-sets/

Note that this has the disadvantage that if you update the stored procedure definition to include more columns, you must remember to track down and update all reports that use it. If your setting doesn't change SP definitions then it's not an issue and is the most reliable way to tell SSRS what the right types are, but for me casting the type inside the SP is more flexible in the long run and is enough for SSRS in every case I've encountered.

ssrs error converting data type nvarchar to int

2 Answers