将varchar值转换为int类型时转换失败 [英] Conversion failed when converting the varchar value to type int

问题描述

这是我的商店程序

  USE  [SSDB2] 
  GO  
  / *   *****对象：StoredProcedure [dbo ]。[SPSearching]脚本日期：25/12/34 09:11:48****** /  
  SET  < span class =code-sdkkeyword> ANSI_NULLS   ON  
  GO  
  SET   QUOTED_IDENTIFIER   ON  
  GO  
   -    ==== =========================================  
 < span class =code-comment>  -   作者：姓名 
   -   创建日期： 
   -   描述： 
   -    == ===========================================  
  ALTER   PROCEDURE  [dbo]。[SPSearching] 
   -   在此处添加存储过程的参数 
  @ Mode   varchar （ 50 ），
  @ WhereClause   varchar （Max）=  null  
  AS  
 如果 @ Mode = '  ColumnNames' 
  BEGIN  
  SELECT  column_name  FROM  i nformation_schema.columns  WHERE  table_name = '  Hadiths_old' 
  END  
 
 如果 @ Mode =  搜索' 
  BEGIN  
 选择 [Hadith_Text] 
，[Hadith_Urdu] 
，[Hadith_English] 
，[Chapter_English_Name] 
，[Chapter_Urdu_Name] 
，[Baab_English_Name] 
，[Baab_Urdu_Name] 
，[Baab_Id] 
，[Hadith_Book_Number] 
 来自 Hadiths_old 
 其中 ID<  34192  + ' ' +  @ WhereClause  
 订单 按 ID 
 
  END  

当我尝试执行它时像这样

 执行 SPSearching  @ Mode  = ' 搜索'， @ WhereClause  = '  Hadith_Book_Number = 1' 

它向我显示此错误

Msg 245，Level 16，State 1，Procedure SPSearching，Line 18

转换varchar时转换失败值'Hadith_Book_Number = 1'到数据类型int。

解决方案

H我的建议是：无论何时需要存储整数数据，都要使用整数数据库类型，而不是 varchar 。在您的情况下，存储的数据可能是不匹配整数字符串格式的字符串。将其作为字符串查询并查看调试器下的内容。

-SA

尝试以下给出的方式 -

 SET ANSI_NULLS ON 
 GO 
 SET QUOTED_IDENTIFIER ON 
 GO 
  -  ============================================ = 
  - 作者：姓名
  - 创建日期：
  - 描述：
  -  ================= ============================ 
 ALTER PROCEDURE [dbo]。[SPSearching] 
  - 添加参数 此处的存储过程
 @Mode varchar（ 50 ），
 @WhereClause varchar（Max）=  null  
 AS 
 如果 @ Mode = '  ColumnNames' 
 BEGIN 
 SELECT column_name FROM information_schema.columns WHERE table_name = '  Hadiths_old' 
 END 
 
 如果 @ Mode = ' 搜索' 
 BEGIN 
声明@SQL VARCHAR（MAX）
 
 SET @ SQL = ' 选择[Hadith_Text] 
，[Hadith_Urdu] 
，[Hadith_English] 
，[Chapter_English_Name] 
，[Chapter_Urdu_Name] 
，[Baab_English_Name] 
，[Baab_Uddu_Name] 
，[Baab_Id] 
，[Hadith_Book_Number] 
来自Hadiths_old 
其中ID< 34192' 
 
 IF @ WhereClause<> ' ' 
 SET @ SQL = @ SQL + '  AND' + @WhereClause + ' 按ID排序 
 
 EXEC（@SQL）
 
 END 

this is my storeprocedure

USE [SSDB2]
GO
/****** Object:  StoredProcedure [dbo].[SPSearching]    Script Date: 25/12/34 09:11:48 ص ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Name
-- Create date: 
-- Description:	
-- =============================================
ALTER PROCEDURE [dbo].[SPSearching] 
	-- Add the parameters for the stored procedure here
	@Mode varchar(50),
	@WhereClause  varchar(Max) = null
AS
if @Mode= 'ColumnNames'
BEGIN
	SELECT column_name FROM information_schema.columns WHERE table_name = 'Hadiths_old'
END

if @Mode= 'Searching'
BEGIN
Select [Hadith_Text]
      ,[Hadith_Urdu]
      ,[Hadith_English]
      ,[Chapter_English_Name]
      ,[Chapter_Urdu_Name]
      ,[Baab_English_Name]
      ,[Baab_Urdu_Name]
      ,[Baab_Id]
      ,[Hadith_Book_Number]
	  From Hadiths_old
	  Where ID < 34192 +'  '+ @WhereClause
	  order by ID

END

when i try to execute it like this

execute SPSearching @Mode ='Searching', @WhereClause = 'Hadith_Book_Number =1' 

it show me this error
Msg 245, Level 16, State 1, Procedure SPSearching, Line 18
Conversion failed when converting the varchar value 'Hadith_Book_Number =1' to data type int.

解决方案

Here is my advice: whenever you need to store integer data, use integer database types, not varchar. In your case, the stored data is probably a string not matching integer string format. Query it as a string and see what it is under debugger.

—SA

Try it the below given way -

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Name
-- Create date: 
-- Description:	
-- =============================================
ALTER PROCEDURE [dbo].[SPSearching] 
	-- Add the parameters for the stored procedure here
	@Mode varchar(50),
	@WhereClause  varchar(Max) = null
AS
if @Mode= 'ColumnNames'
BEGIN
	SELECT column_name FROM information_schema.columns WHERE table_name = 'Hadiths_old'
END
 
if @Mode= 'Searching'
BEGIN
declare @SQL VARCHAR(MAX)

SET @SQL='Select [Hadith_Text]
      ,[Hadith_Urdu]
      ,[Hadith_English]
      ,[Chapter_English_Name]
      ,[Chapter_Urdu_Name]
      ,[Baab_English_Name]
      ,[Baab_Urdu_Name]
      ,[Baab_Id]
      ,[Hadith_Book_Number]
	  From Hadiths_old
	  Where ID < 34192 '

IF @WhereClause<>''
	SET @SQL=@SQL+'  AND '+ @WhereClause + ' order by ID'

EXEC(@SQL)
 
END

这篇关于将varchar值转换为int类型时转换失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！