when Build Dynamic query Error display converting data type varchar to

I work on SQL server 2012 when Build dynamic Query I get error

Msg 8114, Level 16, State 5, Line 16 Error converting data type varchar to float.

so How to solve this Error ?

I build dynamic query based on @Header and @column and @Body .

@Header represent header must display as Headers .

@column represent pivot columns .

@Body represent select query for data .

create table #FinalTable
(
PART_ID nvarchar(50) ,
CompanyName nvarchar(50),
PartNumber nvarchar(50),
DKFeatureName nvarchar(100),
value float,
StatusId int,
DisplayOrder int,
splitFlag bit
)
insert into #FinalTable
values
('1222','Honda','silicon','package','15.50Am',2,5,0),
('1900','MERCEIS','GLASS','family','90.00Am',2,2,1),--have column per Unit on @Header because FlagAllow=1
('5000','TOYOTA','alominia','source','70.20kg',2,1,0),
('8000','MACDA','motor','parametric','50.40kg',2,3,1),--have column per Unit on @Header because FlagAllow=1
('8900','JEB','mirror','noparametric','75.35kg',2,4,0)
DECLARE @Header NVARCHAR(MAX)
SELECT
@Header = STUFF(
(
SELECT ', ' + case when A.splitFlag = 1 and a.value<> '-' and (a.Value is not null) then ''''+A.DKFeatureName +''' as '''+A.DKFeatureName+''','''+ A.DKFeatureName + 'Units' +''' as ''' + A.DKFeatureName +'Units' +'''' else ''''+A.DKFeatureName +''' as ''' + A.DKFeatureName +'''' end
FROM #FinalTable A
where StatusId=2
ORDER BY DisplayOrder
FOR XML PATH ('')
),1,2,''
)
DECLARE @Columns NVARCHAR(MAX)
SELECT
@Columns = STUFF(
(
SELECT ', ' + case when A.splitFlag = 1 and a.value<> '-' and (a.Value is not null) then '['+A.DKFeatureName+'],['+A.DKFeatureName+'Unit]' else quotename(A.DKFeatureName) end
FROM #FinalTable A where StatusId=2
ORDER BY DisplayOrder
FOR XML PATH ('')
),1,2,''
)
DECLARE @Body NVARCHAR(MAX)
SELECT
@Body = STUFF(
(
SELECT ', ' + case when A.splitFlag = 1 and a.value<> '-' and (a.Value is not null) then 'LEFT(' + QUOTENAME (A.DKFeatureName) + ',PATINDEX(''%[^0-9.]%'',' + QUOTENAME (A.DKFeatureName) + '+ ' + ''' ''' + ')-1) as ['+A.DKFeatureName+'],RIGHT('+ QUOTENAME (A.DKFeatureName) +',LEN('+ QUOTENAME (A.DKFeatureName) +') - PATINDEX(''%[^0-9.]%'','+ QUOTENAME (A.DKFeatureName) +')+1) as ['+A.DKFeatureName +'Units'+']' else quotename(A.DKFeatureName) end
FROM #FinalTable A
where StatusId=2
ORDER BY A.DisplayOrder
FOR XML PATH ('')
),1,2,''
)
DECLARE @SQL NVARCHAR(MAX)
select @SQL =CONCAT('
SELECT * Into #NewTable
FROM #FinalTable
PIVOT(max(Value) FOR DKFeatureName IN ('+@Columns+')) AS PVTTable
',
N' Select ''PART_ID'' as ''PART_ID'' ,''CompanyName'' as ''CompanyName'',''PartNumber'' as ''PartNumber'' , ' +@Header + '
union all
select PART_ID,CompanyName,PartNumber, ' +@Body + ' from #NewTable
')
EXEC (@SQL)

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

ahmed elbarbary6y ago
I need to solve that by casting to sqlquery to varchar

because temp table is table have transaction and processesn and i dont have ability to change it
so i need to handle from select query
Jignesh Kumar6y ago
Hi,

Please check you 5th column "value" is float. you need to check in your query where ever it is used. you are getting error in that.