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
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,