INT field - String or binary data would be truncated

Question

first-time poster, long-time fan. I've looked at all the other answers on this topic and can't find anything that solves the problem.

I have some SQL that returns a matrix of qualifications by region using a dynamic pivot. I have the code working correctly, bringing back all rows, but now I want to use a filter.

The errant code is in the WHERE clause: T3.TrainingTypeID = ' + @TrainingID + ' If I hard code a value for T3.TrainingTypeID (this is an alias for tlkpTrainingType.ID), (200, say), I get one row returned, which is what I want but if I assign the same value to variable @TrainingID INT, I get the above error message. I have checked the datatype of tlkpTrainingType.ID and it is definitely INT (And Select Max(Len(TlkpTrainingType.ID)) From TlkpTrainingType returns 3).

I'm not sure I understand triggers all that well, but given that I'm just reading from the DB, am I right in thinking it's not relevant here?

Am I missing something completely obvious? All insights appreciated, I'm a relative newbie!

Here is my code, the relevant WHERE clause is near the bottom:

DECLARE @TrainingID AS INT, @cols AS NVARCHAR(MAX), @cols2 AS NVARCHAR(MAX), @cols3 AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(tlkpRegion.RegionName) FROM tlkpRegion FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,''); SET @cols2 = STUFF((SELECT distinct ', ISNULL(' + QUOTENAME(tlkpRegion.RegionName) + ',0) as' + QUOTENAME(tlkpRegion.RegionName) FROM tlkpRegion FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,''); SET @cols3 = STUFF((SELECT distinct ', SUM(' + QUOTENAME(tlkpRegion.RegionName) + ') as ' + QUOTENAME(tlkpRegion.RegionName) FROM tlkpRegion FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,''); SET @TrainingID = 200; SET @query = ' SELECT T3.TrainingType, T3.TrainingTypeID, ' + @cols3 + ' FROM (SELECT tlkpTrainingType.Name AS TrainingType, tlkpTrainingType.ID AS TrainingTypeID, ' + @cols2 + ' FROM tlkpTrainingType LEFT OUTER JOIN (SELECT TrainingType, TrainingTypeID, ' + @cols + ' FROM (SELECT R0.RegionID, R0.RegionName, T0.TrainingType, T0.TrainingTypeID, T0.CountQuals FROM tlkpRegion AS R0 LEFT OUTER JOIN (SELECT tlkpTrainingType.Name AS TrainingType, tlkpTrainingType.ID AS TrainingTypeID, tlkpTrainingType.ID AS CountQuals, tlkpRegion.RegionName, tlkpRegion.RegionID FROM tblTechnicianTraining INNER JOIN tlkpTrainingType ON tlkpTrainingType.ID = tblTechnicianTraining.TrainingTypeRef LEFT OUTER JOIN tblTechnician ON tblTechnician.TechnicianID = tblTechnicianTraining.TechnicianRef INNER JOIN tlkpRegion ON tlkpRegion.RegionID = tblTechnician.RegionRef WHERE tlkpTrainingType.Deleted = 0 AND tblTechnician.CurrentlyEmployed = 1 AND tblTechnicianTraining.ExpiryDate > GetDate() ) AS T0 ON T0.RegionID = R0.RegionID ) AS T1 PIVOT (COUNT(CountQuals) for RegionName IN (' + @cols + ') ) AS P GROUP BY TrainingType, TrainingTypeID, ' + @cols + ') AS T2 ON T2.TrainingTypeID = tlkpTrainingType.ID ) AS T3 WHERE T3.TrainingTypeID = ' + @TrainingID + ' GROUP BY T3.TrainingType, T3.TrainingTypeID ' EXECUTE (@query)

And here is an example of the result I'm after:

TrainingType ¦ TrainingTypeID ¦ CAD ¦ CSD ¦ DVT ¦ FIN ¦ ITS ¦ IWK HedgeCutter ¦ 200 ¦ 0 ¦ 2 ¦ 0 ¦ 7 ¦ 1 ¦ 0

Please, if you'd like to get an answer, do more effort to make your question easily readable. The guys around should spend time with solving not with hard reading. Thanks — Radim Köhler
– Radim Köhler, Commented Mar 6, 2014 at 9:44

Community · Accepted Answer · 2020-06-20 09:12:55Z

I am surprised this works at all, since you are concatenating an INT variable and an NVARCHAR(MAX) variable, e.g. If I run:

DECLARE @i INT = 0, @n NVARCHAR(MAX); SET @n = N'This is a test' + @i;

I get the error:

Msg 245, Level 16, State 1, Line 4

Conversion failed when converting the nvarchar value 'This is a test' to data type int.

In order to apply the filter you may be best off using sp_executesql along with a parameter, rather than concatenating your value and using EXEC:

So instead of:

SET @Query = '.... WHERE T3.TrainingTypeID = ' + @TrainingID + ' ...' EXEC (@Query);

You could use:

SET @Query = '.... WHERE T3.TrainingTypeID = @TrainingID ...'; EXECUTE sp_executesql @Query, N'@TrainingID INT', @TrainingID;

This is more type safe, means you don't have to cast your int variables to nvarchar to get them into the query and allows for better query plan caching too.

@ GarethD: It worked originally because there was no filter i.e. it was all NVARCHAR(MAX), no @TrainingID in it at all. But this works great, and most importantly, I understand why - thanks GarethD!
And sorry for not being clear enough with the formatting guys - just getting to grips with posting ona coding site. Thanks again, though.

Collectives™ on Stack Overflow

INT field - String or binary data would be truncated

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related