Vryjdfkk

dbo.MatchFirstName(@FirstName) and  dbo.MatchLastName(@LastName) 

are table Value Functions(TVFs).

Problem:

In my Update statement i.e Update #Temp after Insert, I want only those rows to be updated that were inserted via the Insert statement used just above it i.e In first Update statment, only the rows that were inserted by selecting from TVF dbo.MatchFirstNumber.

I have my SQL queries like this:

   select @constVal = FunctionWeight from dbo.FunctionWeights where FunctionWeights.FunctionName = 'MatchFirstName'

    Insert into #Temp2(Rownumber, ValFromFunc) select RowNumber,PercentMatch from dbo.MatchFirstName(@FirstName)

    Update #Temp2 set FuncWeight = @constVal, Percentage = ValFromFunc * @constVal 

    select * from #Temp2





    select @constVal = FunctionWeight from dbo.FunctionWeights where FunctionWeights.FunctionName = 'MatchLastName'

    Insert into #Temp2(Rownumber, ValFromFunc) select RowNumber,PercentMatch from dbo.MatchLastName(@LastName)

    Update #Temp2 set FuncWeight = @constVal, Percentage = ValFromFunc * @constVal 

    select * from #Temp2

It as of now obviously updates all the rows.

Secondly, I tried with the condition where FuncWeight is null i.e

Update #Temp2 set FuncWeight = @constVal, Percentage = ValFromFunc * @constVal where FuncWeight is null

but surprisingly it results in all 1's in FuncWeight Column. What could be the reason?

What could be the workaround for this problem?

You don't need an INSERT / UPDATE. These could each be written as a single INSERT that would only have the values you want. E.g.

select @constVal = FunctionWeight from dbo.FunctionWeights where FunctionWeights.FunctionName = 'MatchFirstName';



INSERT INTO #Temp2(RowNumber, ValFromFun, FuncWeight, percentage)

SELECT RowNumber, PercentMatch, @constVal, PercentMatch * @constVal

from dbo.MatchFirstName(@FirstName)

;

But if you really wanted to capture the output of the INSERT statement, SQL Server supports the OUTPUT clause, which you could dump to another temp table like this:

CREATE TABLE #just_the_ids(some_id int NOT NULL UNIQUE);



INSERT INTO #Temp2(RowNumber, ValFromFun)

    OUTPUT INSERTED.RowNumber INTO #just_the_ids

SELECT RowNumber, PercentMatch from dbo.MatchFirstName(@FirstName);

And then you could use #just_the_ids for your second update.