@@Rowcount always returns 1

[Naoise]

I have a table tblA with 8 rows

tblA
-----
a | b

when I run :
exec sp_RunSQL 'update tblA set a = 8', 1, 0, ''

I always see @@ROWCOUNT printing out 1, yet query analyser will say 8 rows affected. Any ideas?

CREATE PROCEDURE sp_RunSQL(
@SQL varchar(3000)
,@SQLType int
,@RowsAffected int output
,@SQLMessage varchar(1000) output
)
AS
BEGIN

-- SQLType 1:SELECT,2:INSERT,3:UPDATE,4ELETE

EXEC (@SQL)

IF @@ERROR <> 0
BEGIN
SET @SQLMessage = 'Failed to run SQL'
RETURN 1
END
ELSE
BEGIN
SET @RowsAffected = @@ROWCOUNT
SET @SQLMessage = 'Successfully ran SQL'
PRINT @@ROWCOUNT
RETURN 0
END

END

GO

 

[bborissov]

....
DECLARE @lnRowCount int

SET @SQL = 'SELECT .............; SET @MyOut = @@ROWCOUNT'
sp_executesql @SQL, '@MyOut int OUTPUT', @MyOut=@lnRowCount OUTPUT

not tested

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[Naoise]

The answer has to do with the fact that SET @foo = @@ROWCOUNT has to be placed immediately after the EXEC(@SQL) statement otherwise it doesn't work