I created a strored procedure that can update tables in two different databases.
CREATE PROCEDURE [dbo].[usp_Update] ( @sID VARCHAR(max), @sName VARCHAR(max), @sLocation VARCHAR(max) ) AS BEGIN -- Update table1 in database testDB1 -- UPDATE dbo.table1 SET Name = @sName, Location = @sLocation WHERE ID = @sID -- Update table2 in database testDB2 -- UPDATE testDB2.dbo.table2 SET Name = @sName, Location = @sLocation WHERE ID = @sID END table2 contains all data in table1 but not all data in table1 contains in table2.
So, If there's some data in table1 that updated, automatically table2 also updated and if no data in table1 updated, table2 still updated.
There's no error in my queries actually, when I'm tried to update data that only in table2, it returns
(0 row(s) affected)
(1 row(s) affected)
But, every time I tried update data in both tables, it returns
(2 row(s) affected)
(1 row(s) affected)
So, why its said 2 rows affected when there's only one data on table1?
testDB2.dbo.table2); Second question have you tried to run a select (select .... where id=[the id you passed as parm]) command with those parameters on table1 and 2 and see what it returns?table2but no intable1onlytable2updated. 2nd, I've tried that and each tables return one data.table1has two data with one of them is not usable anymore. I need to add more condition so it will return one row