将varchar值'SVGFPPL PONDY'转换为存储过程中的数据类型int时转换失败

In the below query I am passing 2 parameters but hitting this error

在下面的查询中,我传递了2个参数但是遇到了这个错误

"Conversion failed when converting the varchar value 'SVGFPPL PONDY' to data type int."

“将varchar值'SVGFPPL PONDY'转换为数据类型int时转换失败。”

Please help me to overcome this issue..

请帮我解决这个问题..

ALTER PROCEDURE dbo.[Product]   
 -- Add the parameters for the stored procedure here  
 @i_LocationID Varchar(50),  
 @i_ProductTypeID Varchar(50)='0'  
AS  
BEGIN  
 -- SET NOCOUNT ON added to prevent extra result sets from  
 -- interfering with SELECT statements.  
 SET NOCOUNT ON;  

    -- Insert statements for procedure here  
  --/************  Get Purchase Product based on location*************\-- 





     SELECT  P.ProductID, P.ProductName FROM Product P          
  LEFT OUTER JOIN LocationProductMap LPM ON LPM.ProductID = P.ProductID          
     WHERE  LocationID =@i_LocationID  AND (@i_ProductTypeID ='0' OR P.ProductType =  @i_ProductTypeID

     --/************   Product based on location*************\--   

      SELECT P.ProductID,P.ProductName FROM Product P          
  LEFT OUTER JOIN LocationProductMap LPM ON LPM.ProductID = P.ProductID        
     WHERE LocationID=@i_LocationID AND LPM.ProductInFlow = 2  AND  (@i_ProductTypeID ='0' OR P.ProductType=@i_ProductTypeID)  
END  

2 个解决方案

#1

This error is happening because either, or both of LocationProductMap.LocationID or Product.ProductType are integer columns, whereas you are trying to compare them to VARCHAR parameters passed into your PROC. The following MVCE demonstrates this:

发生此错误是因为LocationProductMap.LocationID或Product.ProductType中的任何一个或两者都是整数列,而您尝试将它们与传递到PROC中的VARCHAR参数进行比较。以下MVCE演示了这一点:

CREATE Table dbo.Foo
(ID INT);
GO

CREATE PROC dbo.FindFoo
    @i_Foo VARCHAR(50)
AS
    SELECT * FROM dbo.Foo Where ID = @i_Foo
GO

EXEC FindFoo 'bar'

Msg 245, Level 16, State 1, Procedure FindFoo, Line 19 Conversion failed when converting the varchar value 'bar' to data type int.

消息245,级别16,状态1,过程FindFoo,第19行将varchar值'bar'转换为数据类型int时转换失败。

The solution is to adjust your parameters so that they match the types of the columns. I would suggest using NULL (and not '0') to represent an optional filter on the query. Also, aren't you missing a UNION of the two queries? Otherwise your proc will return two resultsets - i.e. your PROC might look like:

解决方案是调整参数以使它们与列的类型匹配。我建议使用NULL(而不是'0')来表示查询的可选过滤器。另外,你不是错过了两个查询的UNION吗?否则你的proc将返回两个结果集 - 即你的PROC可能看起来像:

CREATE PROC dbo.FindStuff
    @i_FooID INT,
    @i_BarID INT
AS
    SELECT Foo.ID, Foo.Name FROM dbo.Foo WHERE @i_FooID IS NULL OR Foo.ID = @i_FooID

    UNION

    SELECT Bar.ID, Bar.Name FROM dbo.Bar Where @i_BarID IS NULL OR Bar.ID = @i_BarID
GO

#2

try this-

WHERE  convert(varchar(50),LocationID) = @i_LocationID  
AND 
(@i_ProductTypeID ='0' OR convert(varchar(50),P.ProductType) =  @i_ProductTypeID
...
 WHERE convert(varchar(50),LocationID)=@i_LocationID 
 AND 
 LPM.ProductInFlow = 2  
 AND  (@i_ProductTypeID ='0' OR convert(varchar(50),P.ProductType)=@i_ProductTypeID)