How to solve error Conversion failed when converting the nvarchar value to data type integer?

Question

I work on SQL Server 2014 with a dynamic query; I get this error when I run the query:

Conversion failed when converting the nvarchar value '24VAC/DC' to data type int.

I got error when executing this dynamic SQL

EXEC (@SQL)

How to solve this error please?

Data sample

IF OBJECT_ID('dbo.TAllfeatures') IS NOT NULL
    DROP TABLE dbo.TAllfeatures

IF OBJECT_ID('dbo.TCondition') IS NOT NULL
    DROP TABLE dbo.TCondition

IF OBJECT_ID('dbo.TPartAttributes') IS NOT NULL
    DROP TABLE dbo.TPartAttributes

IF OBJECT_ID('dbo.TAllData') IS NOT NULL
    DROP TABLE dbo.TAllData
    
CREATE TABLE [dbo].[TAllfeatures]
(
     [ZPLID] [int] NULL,
     [ZfeatureKey] [bigint] NULL,
     [FeatType] [int] NULL,
     [AcceptedValueID] [int] NULL,
     [IsNumericValues] [int] NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[TAllfeatures] ([ZPLID], [ZfeatureKey], [FeatType], [AcceptedValueID], [IsNumericValues]) 
VALUES (75533, NULL, 0, 0, 0)
INSERT INTO [dbo].[TAllfeatures] ([ZPLID], [ZfeatureKey], [FeatType], [AcceptedValueID], [IsNumericValues]) 
VALUES (75533, 1505730001, 2044, 155, 0)
INSERT INTO [dbo].[TAllfeatures] ([ZPLID], [ZfeatureKey], [FeatType], [AcceptedValueID], [IsNumericValues]) 
VALUES (75533, 1505730011, 2044, 274, 1)

INSERT INTO [dbo].[TAllfeatures] ([ZPLID], [ZfeatureKey], [FeatType], [AcceptedValueID], [IsNumericValues]) 
VALUES (75533, 1505730036, 2044, 271, 0)
    
CREATE TABLE [dbo].[TCondition]
(
     [TradeCodeControlID] [int]  NOT NULL,
     [VersionYear] [int] NULL,
     [Version] [float] NULL,
     [CodeTypeID] [int] NULL,
     [RevisionID] [bigint] NULL,
     [Code] [varchar](20) NULL,
     [ZPLID] [int] NULL,
     [ZfeatureKey] [bigint] NULL,
     [ZfeatureType] [nvarchar](200) NULL,
     [EStrat] [nvarchar](2500) NULL,
     [EEnd] [nvarchar](2500) NULL
) ON [PRIMARY]
    
INSERT INTO [dbo].[TCondition] ([TradeCodeControlID], [VersionYear], [Version], [CodeTypeID], [RevisionID], [Code], [ZPLID], [ZfeatureKey], [ZfeatureType], [EStrat], [EEnd]) 
VALUES (8123, 2020, 26, 849774, 307683692, N'8535400000', 75533, 1505730001, NULL, N'In(''Surge Protector'',''Surge Protector for Media Lines Only'',''Outlet Strip, Surge Protector'',''PDU, Surge Protector'',''Surge Lightning Arrester'',''Surge Arrester'',''Surge Protection Module'',''Lightning Arrester'',''Lightning Current Arrester'',''Protection Device'',''Surge Voltage Equipment'',''Isolated'',''Surge Protection'',''Coaxial'',''Base Element'')', N'')

INSERT INTO [dbo].[TCondition] ([TradeCodeControlID], [VersionYear], [Version], [CodeTypeID], [RevisionID], [Code], [ZPLID], [ZfeatureKey], [ZfeatureType], [EStrat], [EEnd]) 
VALUES (8124, 2020, 26, 849774, 307683692, N'8535400000', 75533, 1505730036, NULL, N'>1000', N'')
    
CREATE TABLE [dbo].[TPartAttributes]
(
     [PartID] [int] NOT NULL,
     [ZfeatureKey] [bigint] NULL,
     [AcceptedValuesOption_Value] [float] NULL,
     [Name] [nvarchar](500) NOT NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[TPartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [Name]) 
VALUES (1128078, 1505730036, 24, N'24VAC/DC')

INSERT INTO [dbo].[TPartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [Name]) 
VALUES (1128078, 1505730001, NULL, N'Surge Voltage Equipment')

INSERT INTO [dbo].[TPartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [Name]) 
VALUES (1128089, 1505730036, 5, N'5V')

INSERT INTO [dbo].[TPartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [Name]) 
VALUES (1128089, 1505730001, NULL, N'Attachment Plug')

INSERT INTO [dbo].[TPartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [Name]) 
VALUES (27912821, 1505730001, NULL, N'Surge Protection Module')

INSERT INTO [dbo].[TPartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [Name]) 
VALUES (27912821, 1505730036, 480, N'480V')

INSERT INTO [dbo].[TPartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [Name]) 
VALUES (32817870, 1505730001, NULL, N'Surge Protector')

INSERT INTO [dbo].[TPartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [Name]) 
VALUES (32817870, 1505730036, NULL, N'120V, 240V')
    
CREATE TABLE dbo.TAllData
(
         PartID INT,    
         Code VARCHAR(20),    
         CodeTypeID INT,    
         RevisionID BIGINT,    
         ZPLID INT,    
         ConCount INT
)

SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
            
DECLARE @ConStr nvarchar(max) = STUFF((SELECT CONCAT(' Or (PM.ZfeatureKey= ', CC.ZfeatureKey , IIF(CC.ZfeatureType='Qualifications',' And AcceptedValuesOption_Value ' , ' And Name ' ) , CAST(EStrat AS NVARCHAR(2500)),')')   --ValueName
                 FROM dbo.TCondition CC  INNER JOIN dbo.TAllfeatures AL with(nolock) ON AL.ZfeatureKey = CC.ZfeatureKey AND AL.IsNumericValues =0
                 FOR XML PATH(''), TYPE).value('(./text())[1]','varchar(max)'),1,3,'')
                
    
                    
                    
    
                    
                 DECLARE @Sql nvarchar(max)= CONCAT('INSERT INTO dbo.TAllData(PartID,Code,CodeTypeID,RevisionID,ZPLID ,ConCount)',' SELECT  PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID,Count(1) as ConCount
                    
                 FROM 
                 dbo.TPartAttributes PM with(nolock) 
                 INNER JOIN    dbo.TCondition Co with(nolock) ON Co.ZfeatureKey = PM.ZfeatureKey ',                
                 'Where (1=1 and  ' ,  @ConStr,' ) Group By PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID ' ,
                 ' Having Count(1)>= ',(SELECT COUNT(1) FROM TCondition with(nolock)))
    
            
            --print @SQL
             EXEC (@SQL)

I try to solve issue but still issue exist

DECLARE @ConStr nvarchar(max)=  STUFF((SELECT CONCAT(' Or (PM.ZfeatureKey= ''', CC.ZfeatureKey , '''', IIF(CC.ZfeatureType='Qualifications',' And AcceptedValuesOption_Value ' , ' And Name ' ) , LEFT(EStrat, 1), '''', SUBSTRING(EStrat, 2, LEN(EStrat) -1), ''')')   --ValueName
                       FROM dbo.TCondition  CC INNER JOIN dbo.TAllfeatures AL ON AL.ZfeatureKey = CC.ZfeatureKey AND AL.IsNumericValues =0
                       FOR XML PATH(''), TYPE).value('(./text())[1]','varchar(max)'),1,3,'')

Answer 1

It appears you do not need dynamic SQL at all here.

It's hard to know what logic you are trying to achieve, owing to a lack of sample data and expected output, and your existing query could do with better formatting. But it looks like it's some kind of relational division.

Note that you should not use WITH (NOLOCK) unless you really know what you are doing. It can give wildly incorrect results.

I also strongly suggest you use table references on each column, especially within subqueries

INSERT INTO dbo.TAllData
    (PartID, Code, CodeTypeID, RevisionID, ZPLID, ConCount)
SELECT
  PartID,
  Code,
  Co.CodeTypeID,
  Co.RevisionID,
  Co.ZPLID,
  Count(1) as ConCount
FROM dbo.TPartAttributes PM
INNER JOIN dbo.TCondition Co ON Co.ZfeatureKey = PM.ZfeatureKey
Where EXISTS (SELECT 1
    FROM dbo.TAllfeatures AL
    WHERE AL.ZfeatureKey = Co.ZfeatureKey
      AND AL.IsNumericValues = 0
      AND IIF(Co.ZfeatureType = 'Qualifications', AcceptedValuesOption_Value, Name) = EStrat
)
Group By
  PartID,
  Code,
  Co.CodeTypeID,
  Co.RevisionID,
  Co.ZPLID
Having Count(1) >= (SELECT COUNT(1) FROM TCondition);