将数据类型varchar转换为bigint时出错 [英] Error converting data type varchar to bigint

问题描述

大家好，



我写了一个查询，通过加入从多个表中获取一些列。

我传递一个参数对于那个存储过程。

SP在没有任何异常的情况下执行少量输入，并且其他输入失败。



查询进行像这样..

Hi All,

I have written a query to get some columns from multiple tables by joining.
I am passing one parameter to that stored procedure.
The SP executing without any exception for few Inputs, and it is failing for other Inputs.

The query goes like this..

SELECT PM.Id [PropId], REPLACE(LTRIM(RTRIM(PM.Caption)), SPACE(1), '_' )  [FieldName], G.Name AS [GroupName], PROP.NAME [Value]
                        FROM PropertyMapping AS PM LEFT OUTER JOIN
                                Property AS P ON P.Id = PM.PropertyId LEFT OUTER JOIN
                                NavMaster_DataType AS DT ON DT.Id = P.DataTypeId inner join
                                NavMaster_Group AS G ON G.Id = P.GroupId LEFT OUTER JOIN
                                NavMaster_Control AS C ON C.Id = P.ControlId LEFT OUTER JOIN
                                Entity AS E ON E.Id = PM.EntityId
                                left outer join EntityType ET on ET.EntityId=E.Id and ET.Id=(Select top 1 EntityTypeId from ProjectList where Id=@ProjectId)
                                left outer join ProjectList PL on PL.EntityTypeId=ET.Id 
                                inner join PropertyDetail PD on PD.ListId=PL.Id and PD.PropertyMappingId=PM.Id
                                left outer join navmaster_properties prop on prop.Id = PD.VALUE
                        WHERE (PM.EntityId = 1) and G.Id=P.GroupId 
                        
                        and PL.Id=@ProjectId and PM.EntityTypeId=(Select top 1 EntityTypeId from ProjectList
                         where Id=@ProjectId)  order by G.name 

在这里，我发现PropertyDetail中的value列是varchar，navmaster_properties中的Id列是bigint。



我尝试使用convert方法将varchar转换为Bigint 。

Here I found one thing the value column from PropertyDetail is varchar and Id column in navmaster_properties is bigint.

I tried to convert varchar to Bigint using convert method.

left outer join navmaster_properties prop on prop.Id = Convert(bigint,PD.VALUE)

但问题仍然存在。



请建议您的想法..



谢谢，

But still the problem exists.

Please suggest your thoughts..

Thanks,

推荐答案

更改您的数据库。

如果您将数值存储为VARCHAR，那么没有好的解决方案可以保证不再出现此问题：您的一个或多个数字值不是数字，因此转换失败。



更改VA RCHAR到一个数字，修复了VARCHAR导致的数据完整性问题，并确保你的INSERT / UPDATE值代码将来只提供数字。

Change your database.
If you are storing numeric values as VARCHAR then there is no "nice" solution that will guarantee that this problem will not occur again: one or more of your "numeric" values is not a number, so the conversion fails.

Change the VARCHAR to a number, fix the data integrity problems that the VARCHAR is causing, and make sure your code to INSERT / UPDATE values only supplies numerics in future.

试试 -

Try-

left outer join navmaster_properties prop on prop.Id = CAST(PD.VALUE AS BIGINT)

希望，它有帮助：）

Hope, it helps :)

试试这个

左外连接navmaster_properties prop on prop.Id =（CASE WHEN（isnumeric（PD.VALUE）= 1）那么CAST（PD.VALUEAS bigint） ELSE 0 END））

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