Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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..

SQL
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 


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.
SQL
left outer join navmaster_properties prop on prop.Id = Convert(bigint,PD.VALUE)


But still the problem exists.

Please suggest your thoughts..

Thanks,
Posted
Updated 25-Mar-15 23:45pm
v2

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.
 
Share this answer
 
Try-
SQL
left outer join navmaster_properties prop on prop.Id = CAST(PD.VALUE AS BIGINT)


Hope, it helps :)
 
Share this answer
 
Comments
[no name] 26-Mar-15 5:57am    
It did not solve.
Try this
left outer join navmaster_properties prop on prop.Id = (CASE WHEN (isnumeric(PD.VALUE) = 1) THEN CAST( PD.VALUEAS bigint ) ELSE 0 END))
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900