May 20, 2010 at 1:56 pm
Hello,
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]
2.GO
3.MERGE INTO mytable as TARGET
4.USING mytable2 as Source
5.ON Target.id=Source.id
6.WHEN MATCHED THEN
7. UPDATE
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.
Thanks,
Vincent D’Souza
May 20, 2010 at 4:41 pm
I am not sure I understand exactly what you are trying to do, but I dont think it is possible to call a stored procedure for each row in a query like that.
Maybe you could convert the calculation to a scalar user defined function instead ?
Or, you might be able to change the bonus procedure so it sets the correct bonus value in the source table before the MERGE.
May 21, 2010 at 6:09 am
No, I cannot use function becuse if I understood correctly I cannot have Insert/update/Deletes in a scalar function OR may be in any function???
May 21, 2010 at 6:20 am
Vincy-856784 (5/21/2010)
No, I cannot use function becuse if I understood correctly I cannot have Insert/update/Deletes in a scalar function OR may be in any function???
So, if I inderstand you correctly, your GetCalculatedBonus procedure has sideeffects - meaning that it inserts/updates/deletes rows.
In that case it is definitely not possible to use a UDF - it is also not possible to call such a procedure for each row in a query.
One suggestion is to modify the GetCalculatedBonus function so it actually calculates the correct bonus for each row in your source table.
You would then call this procedure once and then you would use MERGE to merge the source with the destination.
May 21, 2010 at 8:43 am
Thanks for your quick response. Yes, the calling stored procedure has to insert rows in another table based on the passed in parameter if that table does not have one row for it.
Even the suggestion you provided won't work because i am doing bulk insert from User Define Table Type (UDTT).
The example i specified here is just a basic example and nothing to do with what i am trying to do. But in mine case the situation is more complex than i specified here. I get the collection of rows in the form of UDTT. But the UDTT data is partial data for the update/insert and other partial data is generated based off of other calculation through procedure or through joins.
So, the only thing, I can think of now is using the Cursor for the UDTT and iterate through one by one row and do the Upsert.
Meaning, call the sp based on the each row data from UDTT and store it in a variable and then do the Upsert.
Does that make sense?
May 21, 2010 at 9:05 am
Oh boy! if i use cursor i have to fetch each row into variables. That sucks! can i fetch that row into a table? any syntax???
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply