Tukukkk

I have some SQL within a stored procedure where I am updating a table based on another SELECT statement from a temp table (code below).

SET NOCOUNT ON

DECLARE @RowCount int



UPDATE TABLEX SET 

TRA = ISNULL (ir.DcTra, DCBASIC.TRA),

TRD = ISNULL(CAST(NULLIF(REPLACE(ir.DcTRD, '-', ''), '') AS datetime), DCBASIC.TRD), 

LSINC = ISNULL(ir.DcLsInc, DCBASIC.LSINC), 

REVSWOVR = ISNULL(ir.DcRevswovr, DCBASIC.REVSWOVR) FROM #TempData ir WHERE TABLEX.MEMBNO = ir.IntMembNo



SET @RowCount = @@ROWCOUNT 

The @RowCount variable is being set to 1.

The SELECT of the #TempData table returns no rows and no rows in the TABLEX table are updated (or even exist) with the MembNo (I have added SELECT statements within the sp to debug and they confirm this)

Why is @RowCount being set to 1?

Here is an explanation:

Statements that make a simple assignment always set the @@ROWCOUNT value to 1.

More information you can find here:

@@ROWCOUNT

My example:

CREATE DATABASE FirstDB

GO



USE FirstDB;

GO



CREATE TABLE Person (

    personId INT IDENTITY PRIMARY KEY,

    firstName varchar(20) ,

    lastName varchar(20) ,

    age int

)



INSERT INTO dbo.Person (firstName, lastName, age)

VALUES ('Nick', 'Smith', 30),

    ('Jack', 'South', 25),

    ('Garry', 'Perth', 20)





CREATE TABLE PersonAge (

    personAgeId INT IDENTITY PRIMARY KEY ,

    personId INT ,

    newAge varchar(10)

)



INSERT INTO dbo.PersonAge(personId, newAge)

VALUES (1, 60),

    (2, 65),

    (3, 70)



ALTER TABLE dbo.PersonAge

ADD CONSTRAINT FK_PersonAgePerson FOREIGN KEY (personId)

REFERENCES dbo.Person (personId)

And then example of query:

USE FirstDB;

GO



SET NOCOUNT ON;

DECLARE @row int;



UPDATE Person

SET age = 40

FROM dbo.Person as p join dbo.PersonAge as p1

    ON p.personId = p1.personId

WHERE p.age = 60



SET @row = @@ROWCOUNT

SELECT @row

I create an UPDATE query where none of rows will be affected.

At the end @row consist 0 value.

Here is another example, using INSERT and DELETE--

DECLARE @deletedRows INT = 0;

SELECT @deletedRows = @@ROWCOUNT;   --no previous DML statement

SELECT @deletedRows;    --@@ROWCOUNT = 1 for a simple assignment

GO



DROP TABLE IF EXISTS #Test;

GO

CREATE TABLE #Test (ID INT IDENTITY, CurrentDate DATETIME DEFAULT GETDATE());

GO



INSERT #Test DEFAULT VALUES;    --INSERT a single row

DECLARE @deletedRows INT = @@ROWCOUNT;  --@@ROWCOUNT = 1 

SELECT @deletedRows;

GO



DELETE FROM #Test WHERE 1=2;    --no rows deleted

DECLARE @deletedRows INT = @@ROWCOUNT;  --@@ROWCOUNT = 0

SELECT @deletedRows;

GO



DELETE TOP (1) t FROM #Test t WHERE 1=1;    --1 row deleted

DECLARE @deletedRows INT = @@ROWCOUNT;  --@@ROWCOUNT = 1

SELECT @deletedRows;

GO



DELETE TOP (1) t FROM #Test t WHERE 1=1;    --no rows left to delete

DECLARE @deletedRows INT = @@ROWCOUNT;  --@@ROWCOUNT = 0

SELECT @deletedRows;

GO