how to solve error Conversion failed when converting the nvarchar value '24VAC/DC' to data type int ?

Question

how to solve error Conversion failed when converting the nvarchar value '24VAC/DC' to data type int ?

ahmed salah 3,216

I work on SQL server 2014 I get error when run statement below

error say

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

I got error when execut dynamic sql

EXEC (@alenzi )
so 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 [dbo].[TAllfeatures] ([ZPLID], [ZfeatureKey], [FeatType], [AcceptedValueID], [IsNumericValues]) VALUES (75533, NULL, 0, 0, 0)  
INSERT [dbo].[TAllfeatures] ([ZPLID], [ZfeatureKey], [FeatType], [AcceptedValueID], [IsNumericValues]) VALUES (75533, 1505730001, 2044, 155, 0)  
INSERT [dbo].[TAllfeatures] ([ZPLID], [ZfeatureKey], [FeatType], [AcceptedValueID], [IsNumericValues]) VALUES (75533, 1505730011, 2044, 274, 1)  
INSERT [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 [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 [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 [dbo].[TPartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [Name]) VALUES (1128078, 1505730036, 24, N'24VAC/DC')  
INSERT [dbo].[TPartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [Name]) VALUES (1128078, 1505730001, NULL, N'Surge Voltage Equipment')  
INSERT [dbo].[TPartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [Name]) VALUES (1128089, 1505730036, 5, N'5V')  
INSERT [dbo].[TPartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [Name]) VALUES (1128089, 1505730001, NULL, N'Attachment Plug')  
INSERT [dbo].[TPartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [Name]) VALUES (27912821, 1505730001, NULL, N'Surge Protection Module')  
INSERT [dbo].[TPartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [Name]) VALUES (27912821, 1505730036, 480, N'480V')  
INSERT [dbo].[TPartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [Name]) VALUES (32817870, 1505730001, NULL, N'Surge Protector')  
INSERT [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)

Accepted answer

2 additional answers

Your answer

Answer 1

Hi @ahmed salah ,

Please try:

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,'')  
--print @ConStr  
  
SET @ConStr=REVERSE(REPLACE(REPLACE(REVERSE(@ConStr),'>','''>'),'0001','''0001'))  
  
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)

Regards,
Echo

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

ahmed salah 3,216 Reputation points

2021-12-02T09:08:54.263+00:00

yes correct
thanks very much

Answer 2

Viorel 122.6K

Fix the second sample row of TCondition. The new statements are:

INSERT [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 [dbo].[TCondition] ([TradeCodeControlID], [VersionYear], [Version], [CodeTypeID], [RevisionID], [Code], [ZPLID], [ZfeatureKey], [ZfeatureType], [EStrat], [EEnd]) VALUES (8124, 2020, 26, 849774, 307683692, N'8535400000', 75533, 1505730036, 'Qualifications', N'>1000', N'')

ahmed salah 3,216 Reputation points

2021-12-01T18:20:55.14+00:00

can i change code above to avoid this error

ahmed salah 3,216

what i try

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 ON AL.ZfeatureKey = CC.ZfeatureKey AND AL.IsNumericValues = 0
 FOR XML PATH(''), TYPE).value('(./text())[1]','varchar(max)'),1,3,'');

but issue still exist

ahmed salah 3,216

i also try but issue still 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,'')

ahmed salah 3,216 Reputation points

2021-12-01T19:04:05.453+00:00

as error above
issues may be result of wrong input
so i prefer to handle it from code
so how to solve issue
Viorel 122.6K Reputation points

2021-12-01T20:07:34.97+00:00

Formerly it helped: https://learn.microsoft.com/en-us/answers/questions/158412/.

Answer 3

Guoxiong 8,206

The problem is in the variable @ConStr. If you print out the variable you will get this:

(PM.ZfeatureKey= 1505730001 And Name 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')) Or (PM.ZfeatureKey= 1505730036 And Name >1000)

But Name is the string column in the [dbo].[TPartAttributes] table, so Name >1000 will fail.

I think you can change data N'>1000' to N'>''1000''' so that you will get this:

(PM.ZfeatureKey= 1505730001 And Name 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')) Or (PM.ZfeatureKey= 1505730036 And Name >'1000')

which will work.

ahmed salah 3,216 Reputation points

2021-12-01T19:15:49.84+00:00

yes to solve issue will be
name > '1000'
instead of
name >1000
so how to make my code above
acept '' single quotes to prevent that
please
Guoxiong 8,206 Reputation points

2021-12-01T19:29:54.927+00:00

Replace the following INSERT statement

INSERT [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'')

with

INSERT [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'')
ahmed salah 3,216 Reputation points

2021-12-01T20:18:03.517+00:00

still issue not give me what i need
i need to add single quote from query execution
not from data

Guoxiong 8,206

This is a specific fix for this issue:

DECLARE @ConStr nvarchar(max) =  
    STUFF((SELECT CONCAT(' Or (PM.ZfeatureKey= ', CC.ZfeatureKey, IIF(CC.ZfeatureType = 'Qualifications', ' And AcceptedValuesOption_Value ', ' And Name '), CASE WHEN CHARINDEX('>', EStrat) > 0 THEN REPLACE(EStrat, '>', '>''') + '''' ELSE EStrat END,')')   --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,'')

But how about the case <1000 or =1000? You still need to add more cases to fix it. That was why I would suggest to fix the data issues.

Viorel 122.6K Reputation points

2021-12-01T21:15:34.837+00:00

If you replace Name>1000 with Name>'1000', then you will observe that the result of '24VAC/DC'>'1000' is True. Is this expected?

ahmed salah 3,216

can i change statement below to add single quotes
without display error

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

Guoxiong 8,206 Reputation points

2021-12-01T23:17:03.933+00:00

AS I showed you in the previous post. You cannot just add the single quotes to fix it since the data ">1000" caused the issue. You nee to put the single quotes around the number 1000 without including ">" sign.

Share via

how to solve error Conversion failed when converting the nvarchar value '24VAC/DC' to data type int ?

2 additional answers

Your answer