Rows affected with SQL Server @@ROWCOUNT

SQL Server's @@ROWCOUNT returns the number of rows affected or read by the immediately preceding statement. Read it immediately because later statements can change the value.

Update and read @@ROWCOUNT

Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandText = "UPDATE member SET status = ? WHERE status = ?"
cmd.CommandType = adCmdText
cmd.Parameters.Append cmd.CreateParameter("pNew", adVarWChar, adParamInput, 20, "active")
cmd.Parameters.Append cmd.CreateParameter("pOld", adVarWChar, adParamInput, 20, "pending")
cmd.Execute

Set rs = conn.Execute("SELECT @@ROWCOUNT AS affected_rows")
Response.Write "Rows affected: " & rs("affected_rows")

The historical example updated rows by comparing plaintext passwords. This revision uses neutral status fields instead; password changes should follow the hashing guidance in the authentication tutorials.

For operations that can affect more than two billion rows, SQL Server provides ROWCOUNT_BIG().

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: