sql - INT field - String or binary data would be truncated -

first-time poster, long-time fan. i've looked @ other answers on topic , can't find solves problem.

i have sql returns matrix of qualifications region using dynamic pivot. have code working correctly, bringing rows, want use filter.

the errant code in clause: t3.trainingtypeid = ' + @trainingid + ' if hard code value t3.trainingtypeid (this alias tlkptrainingtype.id), (200, say), 1 row returned, want if assign same value variable @trainingid int, above error message. have checked datatype of tlkptrainingtype.id , int (and select max(len(tlkptrainingtype.id)) tlkptrainingtype returns 3).

i'm not sure understand triggers well, given i'm reading db, right in thinking it's not relevant here?

am missing obvious? insights appreciated, i'm relative newbie!

here code, relevant clause near bottom:

declare @trainingid int, @cols nvarchar(max), @cols2 nvarchar(max), @cols3 nvarchar(max), @query nvarchar(max)   set @cols = stuff((select distinct ',' + quotename(tlkpregion.regionname)              tlkpregion xml path(''), type ).value('.', 'nvarchar(max)')          ,1,1,'');  set @cols2 = stuff((select distinct ', isnull(' + quotename(tlkpregion.regionname) + ',0) as' + quotename(tlkpregion.regionname)       tlkpregion xml path(''), type ).value('.', 'nvarchar(max)')          ,1,1,'');  set @cols3 = stuff((select distinct ', sum(' + quotename(tlkpregion.regionname) + ') ' + quotename(tlkpregion.regionname)       tlkpregion xml path(''), type ).value('.', 'nvarchar(max)')          ,1,1,'');  set @trainingid = 200;  set @query = '  select     t3.trainingtype,     t3.trainingtypeid,     ' + @cols3 + '               (select         tlkptrainingtype.name trainingtype,         tlkptrainingtype.id trainingtypeid, ' + @cols2 + '                  tlkptrainingtype             left outer join              (select             trainingtype,             trainingtypeid, ' + @cols + '                               (select                 r0.regionid,                 r0.regionname,                 t0.trainingtype,                 t0.trainingtypeid,                 t0.countquals                                       tlkpregion r0                      left outer join                      (select                                 tlkptrainingtype.name trainingtype,                          tlkptrainingtype.id trainingtypeid,                         tlkptrainingtype.id countquals,                         tlkpregion.regionname,                         tlkpregion.regionid                                                               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                                                     tlkptrainingtype.deleted = 0 ,                          tbltechnician.currentlyemployed = 1 ,                          tbltechniciantraining.expirydate > getdate()                     ) t0                      on t0.regionid = r0.regionid                 )                  t1                  pivot                  (count(countquals)                     regionname in (' + @cols + ')                 ) p              group             trainingtype,             trainingtypeid, ' + @cols + ') t2             on t2.trainingtypeid = tlkptrainingtype.id             ) t3          t3.trainingtypeid = ' + @trainingid + '          group          t3.trainingtype,         t3.trainingtypeid                                '  execute (@query) 

and here example of result i'm after:

trainingtype ¦ trainingtypeid ¦ cad ¦ csd ¦ dvt ¦ fin ¦ ¦ iwk  hedgecutter  ¦     200        ¦  0  ¦  2  ¦  0  ¦  7  ¦  1  ¦  0   

i surprised works @ all, since concatenating int variable , nvarchar(max) variable, e.g. if run:

declare @i int = 0,         @n nvarchar(max);  set @n = n'this test' + @i; 

i error:

msg 245, level 16, state 1, line 4

conversion failed when converting nvarchar value 'this test' data type int.

in order apply filter may best off using sp_executesql along parameter, rather concatenating value , using exec:

so instead of:

set @query = '....         t3.trainingtypeid = ' + @trainingid + '         ...' exec (@query); 

you use:

set @query = '....         t3.trainingtypeid = @trainingid         ...';  execute sp_executesql @query, n'@trainingid int', @trainingid; 

this more type safe, means don't have cast int variables nvarchar them query , allows better query plan caching too.