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,'')

Honestly, this is why I'm a massive advocate for making the dynamic SQL you produce well formatted too... A single line of SQL that is 600+ characters long isn't easy to read. — Thom A, Dec 01 '21 at 19:09
It's also generally advised to not use syntax such as `EXEC (@SQL);`. Such statements cannot be parametrised, which promote bad habits that result in security flaws like SQL injection. If you need to run a statement that is within a variable or literal string then use [`sys.sp_executesql`](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql). Then you can easily parametrise the statement if you need to. — Thom A, Dec 01 '21 at 19:09
The easiest way to debug dynamic SQL is to `PRINT`/`SELECT` the statement first. Then you can debug that SQL first, and solve the problem before propagating the solution to your SQL that generates the dynamic statement. Often you'll find that the problems are quite simple, such as a typographical error that is difficult to stop in the literal strings, a missing whitespace/linebreak, or leading/trailing delimiters. Taking the time to get the non-dynamic statement working first is really important, as if that doesn't work the dynamic one will have no chance of working correctly. — Thom A, Dec 01 '21 at 19:10
Looking at said `PRINT`ed statement, what does `Name > 1000` mean? How can someone's name have a value greater than the `int` value `1000`? — Thom A, Dec 01 '21 at 19:11
Finally, why are you spamming the `NOLOCK` hint all over your queries here? Why are you using such a hint? `NOLOCK` isn't a "magic go faster button", it's a don't care about wrong data button. I suggest having a read of [Bad habits : Putting NOLOCK everywhere](https://www.sentryone.com/blog/aaronbertrand/bad-habits-nolock-everywhere) — Thom A, Dec 01 '21 at 19:13
Actually, I'm not even sure why the above statement is dynamic; it seems you're using the dynamic statement to inject values for `Name` for the `IN` but you could just just an `EXISTS` for that. The more i look at this, more more I think this is a [XY Problem](//xyproblem.info). — Thom A, Dec 01 '21 at 19:18
query above exactly i need to change it from Name>1000 to Name >'1000' so can you help me . this is actually what i need to change — abeer shlby, Dec 01 '21 at 19:25
My honest answer is, probably get rid of the whole dynamic statement. Why are you using Dynamic SQL at all here? — Thom A, Dec 01 '21 at 19:30
*"because i have parameters variables"* Huh? Parameters are allowed in normal statements... — Thom A, Dec 01 '21 at 19:41
[Dup on MS Q&A](https://learn.microsoft.com/en-us/answers/questions/648029/how-to-solve-error-conversion-failed-when-converti.html) — SMor, Dec 01 '21 at 21:40
Aside... `VALUES`, it's plural. You can insert multiple rows in a single statement by comma-delimiting them, e.g.: `insert dbo.Example (A, B) values ('a', 1), ('b', 2), ... , ('z', 26);` — AlwaysLearning, Dec 01 '21 at 23:24

score 1 · Accepted Answer · answered Dec 02 '21 at 09:23

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);

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

1 Answers1