@@Rowcount Zero After Update, even though rows were updated
Posted on 2007-10-16
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
EXECUTE sp_executesql @SQL
SELECT @ErrorCode = @@ERROR, @RowCount = @@ROWCOUNT
IF @ErrorCode <> 0
PRINT 'Unable to perform update. Error #' + LTRIM(STR(@ErrorCode))
-- At this point I want to record the number of rows inserted into table (logging)
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.