July 5, 2016 at 8:05 am
Hi Team,
I have a requirement where in I need to find a row based on amount for each of my records and update the amounts in another table before calling the next row.
I tried to do this in a function, but Function does not allow Updating tables and I cannot call SP inside my update statements.
I am trying to avoid loops for this operation. My Question:
> Is there a way in SQL to call a SP within update statement
> Is there a way we can update another table in the update statement before moving to next record.
Here is my data structure :
Table1:
SID Name Amount
1 ABC 10000
1 DEF 20000
3 GHI 30000
Now when I call say select dbo.fetchAmount(1,200) , then it should return the least amount and update 200 to it, so expected output is:
SID Name
1 ABC
and Table will now be:
SID Name Amount
1 ABC 12000
1 DEF 20000
3 GHI 30000
So when next update statment is called Select dbo.Fetch(1,300) , it will return the below
SID Name
1 DEF
Let me know your thoughts.
July 5, 2016 at 8:49 am
> Is there a way in SQL to call a SP within update statement
No, you can only have an update statement within a stored procedure. UPDATE is a statement, a Stored Procedure is a set of statements.
> Is there a way we can update another table in the update statement before moving to next record.
Yes, but that's row by row processing and it's slow. In SQL, we usually prefer to write code to handle sets, not rows. In other words, stop thinking on what you want to do to a row and think about what you want to do to a column.
How do you define that the first will affect the ABC row and the following the DEF? Why does the ABC row changes from 10000 to 12000 when you used 200? You mentioned 2 tables but only showed one. Please read the articles linked in my signature to learn how to get better answers.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply