I have stored procedure with the User Defined Table Type (UDTT), which is receiving collection rows from the Sync routines we wrote.
I have to update the table from the rows I received by UDTT parameter using the Upsert command as shown below:
1. USE [MyDatabase]
3. MERGE INTO mytable as TARGET
4. USING mytable2 as Source
5. ON Target.id=Source.id
6. WHEN MATCHED THEN
8. SET Target.name=Source.name,
9. Target.Salary = Source.Salary
10. target.bonus = GetCalculatedBonus Source.id, source.salary
11. WHEN NOT MATCHED THEN
12. INSERT (id,name,salary, bonus)
13. VALUES (Source.id,Source.name,Source.Salary, StoredProcedure Call);
The tricky part is one of the column value should come from the StoredProcedure based on the parameters passed in. The storedprocedure has some DML commands (select and insert based on the situation)
But I cannot find the right way to call that stored procedure to comply with this query. The line 10 throws a syntax error and not sure how to satisfy that syntax. I tried all the tricks I know of. If I change that with EXEC target.bonus = GetCalculatedBonus Source.id, source.salary and didn’t work. Then I tried target.bonus = EXEC GetCalculatedBonus Source.id, source.salary , it doesn’t like this too.
So, I greatly appreciate if anyone could guide me on how to satisfy this syntax.