I have an update statement that I execute via SP_EXECUTESQL and need to get the number of rows affected back.  For some reason this is returning 0 rows affected.  What is the proper way to get the rows affected from invocation of SP_EXECUTESQL in this case?

the following snippet is the sp:

--Input Parameters	
@RepoId INT,
@ObjectId INT,
@FileText NVARCHAR(MAX)
				
AS

DECLARE @RID INT
DECLARE @OID INT
DECLARE @FT NVARCHAR(MAX)
DECLARE @RC INT

SET @RID = @RepoID
SET @OID = @ObjectId
SET @FT = @FileText

DECLARE @OTable NVARCHAR(6)
DECLARE @ParamDefs NVARCHAR(500)
DECLARE @SQL NVARCHAR(4000)

SET @OTable = 'O' + CAST(@RID AS VARCHAR(5))
SET @SQL = 'UPDATE ' + @OTable + ' SET FileText = @FileText WHERE ObjectID = @ObjectId;' 			
SET @ParamDefs = N'@FileText NVARCHAR(MAX), @ObjectId INT'

EXEC SP_EXECUTESQL @SQL, @ParamDefs, @FT, @OID
SET @RC = @@ROWCOUNT
RETURN @RC

thanks

sql2008

• Edited by scott_m 13 hours 8 minutes ago

Hi scott_m.

Run the stored procedure like this:

DECLARE @RC int
EXEC @RC = sp_update ...
SELECT 'Return Status' = @RC

SET @OTable = 'O' + CAST(@RID AS VARCHAR(5))
SET @SQL = 'UPDATE ' + @OTable + ' SET FileText = @FileText WHERE ObjectID = @ObjectId;
SELECT @rowc = @@rowcount'
SET @ParamDefs = N'@FileText NVARCHAR(MAX), @ObjectId INT, @rowc int OUTPUT'

EXEC SP_EXECUTESQL @SQL, @ParamDefs, @FT, @OID, @RC OUTPUT
RETURN @RC

That is, you capture @@rowcount inside the dynamic SQL and return it in an output parameter.

By the way, conventionally the RETURN statement is used for indicating success/failure, with 0 = success and anything else meaning failure. Thus, it is better to return the rowcount in an output parameter or as a result set.