January 7, 2005 at 11:33 am
Hello,
I need to update a field in each of 2 joined tables. I have done this before with a stored procedure similar to the following containing 2 update queries, but am not sure if this is the best practice from the standpoint of allowing SQL Server to utilized stored execution plans.
CREATE PROCEDURE dbo.usp_UpdateField1 @OrderID varchar(10)
AS
SET NOCOUNT ON
BEGIN
update t1 set t1.field1 = t2.field1
from table1 t1
join table2 t2
on t1.field2 = t2.field2 AND t1.OrderID = t2.OrderID
where t1.OrderID = @OrderID AND coalesce(t1.Field1, '') = ''
update t2 set t2.field1 = t1.field1
from table2 t2
join table1 t1
on t1.field2 = t2.field2 AND t1.OrderID = t2.OrderID
where t2.OrderID = @OrderID AND t1.field1 <> t2.Field1
END
Which is the best practice in this situation?
a. Perform both updates in a single procedure like this.
b. Write 2 separate procedures calling the 2nd from the first (or calling both from a third procedure or function.)
c. Write 2 separate procedures calling each individually from the app.
I am sure that neither update will ever be performed by itself without also doing the other one, so that is no reason to separate them.
Appreciate any input available.
January 7, 2005 at 11:52 am
If as you mention these are considered a unit of work the single sp approach is correct in my opinion because
- Single Round Trip
- Logical Grouping (From The App Point of view)
and
- not very complex logic.
There are some points to be considered on the code you just posted.
1. You should utilize Transaction management and Error handling logic to accomplish that goal
2. when you are comparing fields with the "<>" operator make sure that either the Fields do not allow Nulls OR if they do include the extra statements to account for it
HTH
* Noel
January 7, 2005 at 12:20 pm
Thanks Noel,
I have learned how to do most of the things I need to in T-SQL, but now I am trying to learn how to do them right.
Thanks for your input.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply