SQL Server T-SQL Rowcount


Microsoft’s T-SQL has a global variable called @@ROWCOUNT that returns the number of rows affected by the last statement. This can be very handy in your sql code or stored procedures for returning the number of rows affected and then using an IF statement afterword to perform some logic. MSDN gives the following example code to illustrate ow this works, however, be careful with @@ROWCOUNT. The following code may not work!

USE AdventureWorks2012;  
GO  
UPDATE HumanResources.Employee   
SET JobTitle = N'Executive'  
WHERE NationalIDNumber = 123456789  
IF @@ROWCOUNT = 0  
PRINT 'Warning: No rows were updated';  
GO  

Notice the comment at MSDN just under the example that says that “Note that IF statements also reset @@ROWCOUNT…”. So the above code may not work for you. You will need to declare a variable, set the value of ROWCOUNT to that variable and use the variable in the IF condition as follows. Also, be aware that the GO statement also resets the ROWCOUNT variable so do not use GO between the UPDATE and IF in the above example.

USE AdventureWorks2012;  
GO  
UPDATE HumanResources.Employee   
SET JobTitle = N'Executive'  
WHERE NationalIDNumber = 123456789  
DECLARE @numRows as int
SET @numRows = @@ROWCOUNT
IF @numRows = 0  
PRINT 'Warning: No rows were updated';  
GO  

T-SQL Global Variables

Global variable names begin with a @@ prefix. You do not need to declare them, since the server constantly maintains them. They are system-defined functions and you cannot declare them. There are many of them. Below are just a few that may come in handy

SELECT @@SERVERNAME;
SELECT @@VERSION  -- SQL Server version and build (not SSMS version!)
SELECT @@CONNECTIONS AS 'The number of logins or attempted logins since SQL Server was last started:'
SELECT @@MAX_CONNECTIONS AS 'Max Simultaneous Connections'
SELECT @@SPID AS 'Server process ID number of the current process'
SELECT SYSTEM_USER AS 'Login Name'
SELECT USER AS 'User Name'

Leave a comment

Your email address will not be published. Required fields are marked *