0

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?

4
  • First question, are dbo.table1 the one you want to update? I ask because you are specifying the database in table2 (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? Commented May 3, 2017 at 4:27
  • @JorgeCampos 1st, if there's data that can be updated in both tables, the data in both table updated, but if there's data in table2 but no in table1 only table2 updated. 2nd, I've tried that and each tables return one data. Commented May 3, 2017 at 4:35
  • 1
    There is no mystery bug here. You need to take a good look at your data and understand why two rows were updated. I suggest you use OUTPUT to capture what was updated, or you could even just select it in your SP stackoverflow.com/questions/16847297/… Commented May 3, 2017 at 4:59
  • Thanks @Nick.McDermaid it returns two rows because in table1 has two data with one of them is not usable anymore. I need to add more condition so it will return one row Commented May 3, 2017 at 6:19

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.