将varchar值'njtestagt'转换为数据类型int时转换失败

I am struggling with few days with the issue.

我正在努力解决这个问题。

I can't figure out how to fix it.

我无法弄清楚如何解决它。

Declare @DbName varchar (50)
Declare @AgencyID varchar(50)

set @DbName='UPC_qat_NewBusiness'
set @AgencyID='1234568'

SET @Query = N'DELETE FROM ' + @DbName + '.dbo.PMSPAG00 WHERE AGNMMCO IN (select MasterCompanyId from ISLocation isLoc join ISAgencyLocation IsAg on IsAg.LocationIndexNbr=isLoc.LocationIndexNbr where AgencyId='+@AgencyID+') and AGNMNBR='+@AgencyID
Execute(@Query)

Why I am getting this error :

为什么我收到此错误：

  "Conversion failed when converting the varchar value 'njtestagt' to data type int"

There is nothing in it or that value in the database records. Please help me anyone.

数据库记录中没有任何内容或该值。请帮助我任何人。

Thanks in Advance.

提前致谢。

1 个解决方案

#1

Change the set statement of agencyid

更改agencyid的set语句

set @AgencyID='''1234568'''

要么

SET @DbName='UPC_qat_NewBusiness'
SET @AgencyID='1234568'
SET @Query = N'DELETE FROM ' + @DbName
             + '.dbo.PMSPAG00 WHERE AGNMMCO IN (select MasterCompanyId from ISLocation isLoc 
join ISAgencyLocation IsAg on IsAg.LocationIndexNbr=isLoc.LocationIndexNbr    
 where AgencyId='''+ @AgencyID + ''') and AGNMNBR=''' + @AgencyID+ '''' --here

Since the Agencyid is a varchar column the values passed to that column should be enclosed with single quotes.

由于Agencyid是varchar列，因此传递给该列的值应该用单引号括起来。

Note : Use print statement to debug the dynamic query

注意：使用print语句调试动态查询

#1