Uexpected values inserted in table I have a table called FunctionWeights which contains data as follows:



My query is as follows:

select @constVal = FunctionWeight from dbo.FunctionWeights where FunctionWeights.FunctionName = 'MatchCity'
Insert into #Temp2 select RowNumber,PercentMatch,@constVal, PercentMatch * @constVal from dbo.MatchCity(@City)
select * from #Temp2

select @constVal = FunctionWeight from dbo.FunctionWeights where FunctionWeights.FunctionName = 'MatchSSN'
Insert into #Temp2 select RowNumber,PercentMatch, @constVal, PercentMatch * @constVal from dbo.MatchSSN(@SSN)
select * from #Temp2 


and so on................ for all function names.

Data-types for columns of #Temp2 are as follows:

Rownumber int not null,
ValFromFunc float(3)null,
FuncWeight float(3) null,
Percentage float(3) not null


However, The #Temp2 table after every insert has only 1 in FunctionWeight column for all the functionNames matched as shown above in queries.

The snapshot of #Temp2 is as follows after insert for any function Name i.e FunctionWeight does not change as it should.
If the FuncWeight field in the #Temp2 table is truly defined as float(3), then the most likely problem is that the datatype of @constVal is DECIMAL(x, 0) or just DECIMAL. Either of those would round up the 0.7 values to be just 1, though the 0.3 and 0.35 values would round down to 0.

Nothing in that posted code or data indicates what could force all values of FunctionWeight to come out as 1. Since some of the FunctionWeight values are 1, I have to assume that the "snapshot of #Temp2" image was taken after testing FunctionName values that have non-1 values for FunctionWeight.

Another possibility is that we are not being shown all rows in the FunctionWeights table and that there is duplication of the FunctionName values where the duplicated rows have a value of 1 for FuncWeight. In that case the last record read by the select @constVal = FunctionWeight from dbo.FunctionWeights query would be the value stored in @constVal, though that would not always be the "duplicated" row since there is no ORDER BY on the query.