tsql rowcount without executing update

Question

I have got any TSQL UPDATE statement and I'd like to know the number of rows that would be affected or the @@ROWCOUNT before really executing the updates.

I think I could replace the UPDATE part of the query with a SELECT COUNT(1), but it seems to me there should be an easier way. Is there an easier way?

If there is no easy solution in SQL, a solution in .NET/C# would be ok for me too; I'm using System.Data.SqlClient.SqlCommand to execute the command anyway. I'm using MSSQL 2008.

Example:

create table T (
    A char(5),
    B int
)

insert into T values ('a', 1)
insert into T values ('A', 1) 
insert into T values ('B', 2)
insert into T values ('X', 1)
insert into T values ('D', 4)


-- I do not want to execute this query
--      update T set A = 'X' where B = 1
-- but I want ot get the @@ROWCOUNT of it, in this case
-- the wanted result would be 3.

There does not seem to be any particularly fast way to do this in SQL-Server, o wise Andrew. — George Menoutis, Oct 17 '18 at 11:19
@Vladislav: I hoped to get a better solution than those in the mentioned duplicate. But yes, it is in effect the same question. — AndreasW, Oct 17 '18 at 11:33

score 1 · Accepted Answer · answered Oct 17 '18 at 11:23

One method would be to use transactions:

begin transaction;
    declare @rc int;
    update T set A = 'X' where B = 1;

    set @rc = @@rowcount;
    . . .
commit;  -- or rollback

Just about any other method would have race conditions, if other threads might be updating the table.

However, I am suspicious that this solves a real problem. I suspect that your real problem might have a better solution. Perhaps you should ask another question explaining what you are really trying to do.

Kevin M. Lapio · Answer 2 · 2018-10-17T11:26:19.753

0

You could wrap your script in a transaction and use ROLLBACK at the end to forgo saving your changes.

create table T (
    A char(5),
    B int
)

insert into T values ('a', 
insert into T values ('A', 1) 
insert into T values ('B', 2)
insert into T values ('X', 1)
insert into T values ('D', 4)

BEGIN TRANSACTION;

    update T set A = 'X' where B = 1
    SELECT @@RowCount;

ROLLBACK TRANSACTION;

When you are ready to save your changes, switch ROLLBACK to COMMIT and execute, or you can simply remove those lines. You can also name your transactions. Ref: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/transactions-transact-sql

You could add a SELECT * FROM T; after the rest of the script to confirm that your object was not actually updated.

edited Oct 17 '18 at 11:26

answered Oct 17 '18 at 11:22

Kevin M. Lapio

378
1
4
8

This still using select , as OP said he is trying to avoid that – sagi Oct 17 '18 at 11:25
I interpreted that as he didn't want to manually switch his `UPDATE` to `SELECT` in order to test. In my response, I added a separate select in an uncommitted transaction. In any case, my original answer was still wrong, because I omitted his commented out `UPDATE` and wrapped the `CREATE` and `INSERT` statements, instead! – Kevin M. Lapio Oct 17 '18 at 11:28

tsql rowcount without executing update

2 Answers2