Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

@@Rowcount Zero After Update, even though rows were updated

Posted on 2007-10-16
8
Medium Priority
?
3,729 Views
Last Modified: 2011-04-14
I actually have a couple of questions regarding the following code, one is more out of curiousity.

Within a stored procedure I have an update being made on a table which is passed as a parameter.  Therefore I create a SQL string to do the update etc......(I have simplied the stored procedure so you get the idea of what i want to achieve)

===============================================
DECLARE @SQL AS NVARCHAR(500)
DECLARE @TableName AS VARCHAR(20)
DECLARE @ErrorCode AS INT
DECLARE @RowCount  AS INT

SET @TableName = "Customer2005"

SET @SQL = ""
SET @SQL = @SQL + "BEGIN TRANSACTION " + CHAR(13)
SET @SQL = @SQL + "  UPDATE " + @TableName + " "  + CHAR(13)
SET @SQL = @SQL + "     SET extracted = 1,  " + CHAR(13)
SET @SQL = @SQL + "         closedissue = 1 " + CHAR(13)
SET @SQL = @SQL + "   WHERE extracted = 0   " + CHAR(13)
SET @SQL = @SQL + "     AND closedissue = 0 " + CHAR(13)  
SET @SQL = @SQL + "     AND province = 'Ontario' " + CHAR(13)  
SET @SQL = @SQL + "COMMIT TRANSACTION "

IF (SELECT count(*) FROM GCTI_500..sysobjects WHERE xtype = 'U' AND name = @TableName) > 0
BEGIN
  EXECUTE sp_executesql @SQL
            
  SELECT @ErrorCode = @@ERROR, @RowCount = @@ROWCOUNT
                   
  IF @ErrorCode <> 0
     PRINT 'Unable to perform update.  Error #' + LTRIM(STR(@ErrorCode))
  ELSE
     -- At this point I want to record the number of rows inserted into table (logging)
END
===============================================

At the point in which I run the select to capture the error code and row count, even though the update was successful, rowcount is zero.  Because this is a stored procedure, at the start of the stored procedure I "SET NOCOUNT ON".  However I dont believe this should have an affect because later in the stored procedure I test "IF @@RowCount > 0" after a SELECT query which will sometimes return true.

Is it because it is doing the UPDATE as part of a string?  Is there a way to grab the RowCount despite this?

Part 2 - Just Curious Part

In the UPDATE string, you see I have BEGIN TRAN and COMMIT TRAN.  Should they be part of the string or should they surround the "EXECUTE sp_executesql @SQL" or does it matter at all?


Many thanks in advance.
0
Comment
Question by:GemmaCom
8 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 20090281
@@ROWCOUTN returns the rows affected from the last statement.

In this case the last statement is EXEC sp_executeSQL... which affects zero rows.

It's doesn't actually count the rows affected by the statement inside sp_executeSQL.

0
 
LVL 30

Assisted Solution

by:nmcdermaid
nmcdermaid earned 75 total points
ID: 20090304
regaring BEGIN and END tran.... there is only one statement between them, so they don't do anything.

An UPDATE by itself is a transaction. It either happens or it doesn't.

If you had two statements in there then it would be worth doing a transaction.

BEGIN TRAN
UPDATE... something
INSERT... something else
COMMIT TRAN


now... the update and insert are in 'no mans land' until the insert completes. The COMMIT TRAN pushes them both into the database.


transactions are normally used like this though:


BEGIN TRAN
UPDATE... something

IF @@ERROR <> 0
BEGIN
  ROLLBACK TRAN
  RETURN
END

INSERT... something else

IF @@ERROR <> 0
BEGIN
  ROLLBACK TRAN
  RETURN
END


COMMIT TRAN


In this case BOTH the INSERT and UPDATE have to work. If the UPDATE works but the INSERT fails, the transaction will be rolled back, which means that the UPDATE won't be seen.

This is a way of making sure that work is performed in a unit.
0
 

Author Comment

by:GemmaCom
ID: 20090343
RE: The transaction - sorry I over simplified the code in that I even removed additional statements that would appear in addition to the UPDATE.  I included it because I was curious if it should be inside the string or outside or if it matters.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:GemmaCom
ID: 20090351
I cut the cut down to basics and sp_executesql does return a rowcount :

DECLARE @SQL AS NVARCHAR(500)
DECLARE @ErrorCode AS INT
DECLARE @RowCount  AS INT

SET @SQL = "SELECT NAME FROM sysobjects WHERE xtype = 'U' " + CHAR(13)

EXECUTE sp_executesql @SQL
            
SELECT @ErrorCode = @@ERROR, @RowCount = @@ROWCOUNT
                   
PRINT @ROWCOUNT -- Returns 29 (and there are 29 user tables in the particular DB)
0
 
LVL 39

Accepted Solution

by:
appari earned 300 total points
ID: 20090358
@@ROWCOUTN returns the rows affected from the last statement.

in your first batch the last statement is commit transaction and its actually affecting 0 rows so the @@rowcount  returns 0.
if you remove the transaction statements to outside sp_executesql you will get proper count.
0
 

Author Comment

by:GemmaCom
ID: 20090436
Appari - thanks, quite right.  Which means there is no way of getting the rowcount when part of a transaction.  Final example....

SET @SQL = @SQL + "BEGIN TRAN "
SET @SQL = @SQL + " INSERT something "
SET @SQL = @SQL + " DELETE something "
SET @SQL = @SQL + "COMMIT TRAN "

EXECUTE sp_executesql @SQL

So you wouldnt have any way of getting the row count from the last statement, if it was DELETE or UPDATE is there.
0
 
LVL 15

Expert Comment

by:dbbishop
ID: 20090726
sp_executesql lets you define OUTPUT parameters, so you could modify your dynamic SQL to return a @Rowcount value back to the calling procedure. Your execute statement would then read

execute sp_executesql @SQL, @Rowcount OUTPUT
0
 

Expert Comment

by:etagroup
ID: 22459048
It seems the reason for the dynamic SQL is to choose a table to update at runtime. If that is the case, check out SYNONYMS. You can simply set the SYNONYM dynamically at runtime, and then your sql statements will no longer need to be dynamic. This is MUCH easier. (SQL2005 or greater)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question