Dynamic SQL and @@Rowcount

13 Jan 2006 9:47 PM

dpc

How can I get the RowCount of a SQL that has been executed in a dynamic SQL.

@@RowCount appears to return the rowcount for the EXEC of sp_executesql.

T.I.A.

E.g.:

DECLARE @TblGen Table
(relation nvarchar(50),
   attribute nvarchar(50),
   sql nvarchar(255),
   rtncde int,
   cnt bigint,
   remarks nvarchar(80) )

DECLARE
   @Relation nvarchar(50),
   @Attribute nvarchar(50),
   @RtnCde      integer,
   @RecCnt    integer

Insert into @tblGen
(relation, attribute, sql)
select so.name, sc.name, 'select count(*) as ''Reccnt for ' +
left(so.name,25) + ''' from ' + left(so.name,25)
   + ' where ' + ltrim(sc.name) + ' is not null '
from sysobjects so, syscolumns sc
where so.id = sc.id
and so.type = 'U'
and sc.name like '%rep%'
and left(so.name,3) not in ('wrk','rpt', 'CFT')
group by
so.name, sc.name,
'Select count(*) as ''Reccnt for ' + left(so.name,25) + ''' from ' +
left(so.name,25)
   + ' where ' + ltrim(sc.name) + ' is not null '
DECLARE SQLCsr Cursor For
Select relation, attribute, sql from @tblGen
FOR Update

OPEN SQLCsr

DECLARE @SQLStmt as nvarchar(255)
Fetch SQLCSR into @Relation, @Attribute, @SQLStmt

WHILE @@FETCH_STATUS = 0
   BEGIN
    Print @SQlStmt
    EXECUTE @Rtncde = sp_executesql @stmt = @SQLStmt
    UPDATE @tblGEN
        SET RtnCde = @RtnCde,
            Cnt    = @@Rowcount
          WHERE current of SQLCSR
    Fetch NEXT From SQLCSR into @Relation, @Attribute, @SQLStmt
   END

CLOSE SQLCsr
DEALLOCATE SQLCsr

13 Jan 2006 9:52 PM

Bob Barrows [MVP]

Use an output parameter:
USE pubs

DECLARE @RowCount int

EXEC sp_executesql
    N'SELECT @RowCount = COUNT(*) FROM authors',
     N'@RowCount int OUTPUT',
     @RowCount OUTPUT

RAISERROR ('Authors rowcount is %d', 0, 1, @RowCount)

dpc wrote:
Show quote

> How can I get the RowCount of a SQL that has been executed in a
> dynamic SQL.
>
> @@RowCount appears to return the rowcount for the EXEC of
> sp_executesql.
>
>
> T.I.A.
>
> E.g.:
>
> DECLARE @TblGen Table
>   (relation nvarchar(50),
>    attribute nvarchar(50),
>    sql nvarchar(255),
>    rtncde int,
>    cnt bigint,
>    remarks nvarchar(80) )
>
>
> DECLARE
>    @Relation nvarchar(50),
>    @Attribute nvarchar(50),
>    @RtnCde      integer,
>    @RecCnt integer
>
> Insert into @tblGen
>   (relation, attribute, sql)
> select so.name, sc.name, 'select count(*) as ''Reccnt for ' +
> left(so.name,25) + ''' from ' + left(so.name,25)
>    + ' where ' + ltrim(sc.name) + ' is not null '
> from sysobjects so, syscolumns sc
> where so.id = sc.id
> and so.type = 'U'
> and sc.name like '%rep%'
> and left(so.name,3) not in ('wrk','rpt', 'CFT')
> group by
> so.name, sc.name,
> 'Select count(*) as ''Reccnt for ' + left(so.name,25) + ''' from ' +
> left(so.name,25)
>    + ' where ' + ltrim(sc.name) + ' is not null '
> DECLARE SQLCsr Cursor For
>   Select relation, attribute, sql from @tblGen
> FOR Update
>
> OPEN SQLCsr
>
> DECLARE @SQLStmt as nvarchar(255)
> Fetch SQLCSR into @Relation, @Attribute, @SQLStmt
>
> WHILE @@FETCH_STATUS = 0
>    BEGIN
> Print @SQlStmt
> EXECUTE @Rtncde = sp_executesql @stmt = @SQLStmt
> UPDATE @tblGEN
> SET RtnCde = @RtnCde,
>     Cnt    = @@Rowcount
>           WHERE current of SQLCSR
> Fetch NEXT From SQLCSR into @Relation, @Attribute, @SQLStmt
>    END
>
>
> CLOSE SQLCsr
> DEALLOCATE SQLCsr

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

13 Jan 2006 10:24 PM

Louis Davidson

Try something like this in your loop:

declare @insertString nvarchar(2000), @outCount int, @outRowCount int,
        @tableName sysname, @columnName sysname
set @tablename = 'information_schema.tables'
set @columnname = 'table_name'

set @insertString =
'select @count = count(*)
from ' + @tableName + '
where ' + @columnName + ' is not null
set @rowcount = @@rowcount'

EXEC sp_executesql @InsertString,
     N'@count int output, @rowcount int output',
     @count = @outCount output, @rowcount = @outRowcount output

select @outCount as [count], @outRowcount as [rowcount]

It uses output parms from the sp_executesql to do what you want.

--
----------------------------------------------------------------------------
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)

Show quote

"dpc" <d**@discussions.microsoft.com> wrote in message
news:74530707-6CD3-4190-B42D-93481400C810@microsoft.com...
> How can I get the RowCount of a SQL that has been executed in a dynamic
> SQL.
>
> @@RowCount appears to return the rowcount for the EXEC of sp_executesql.
>
>
> T.I.A.
>
> E.g.:
>
> DECLARE @TblGen Table
> (relation nvarchar(50),
>   attribute nvarchar(50),
>   sql nvarchar(255),
>   rtncde int,
>   cnt bigint,
>   remarks nvarchar(80) )
>
>
> DECLARE
>   @Relation nvarchar(50),
>   @Attribute nvarchar(50),
>   @RtnCde      integer,
>   @RecCnt integer
>
> Insert into @tblGen
> (relation, attribute, sql)
> select so.name, sc.name, 'select count(*) as ''Reccnt for ' +
> left(so.name,25) + ''' from ' + left(so.name,25)
>   + ' where ' + ltrim(sc.name) + ' is not null '
> from sysobjects so, syscolumns sc
> where so.id = sc.id
> and so.type = 'U'
> and sc.name like '%rep%'
> and left(so.name,3) not in ('wrk','rpt', 'CFT')
> group by
> so.name, sc.name,
> 'Select count(*) as ''Reccnt for ' + left(so.name,25) + ''' from ' +
> left(so.name,25)
>   + ' where ' + ltrim(sc.name) + ' is not null '
> DECLARE SQLCsr Cursor For
> Select relation, attribute, sql from @tblGen
> FOR Update
>
> OPEN SQLCsr
>
> DECLARE @SQLStmt as nvarchar(255)
> Fetch SQLCSR into @Relation, @Attribute, @SQLStmt
>
> WHILE @@FETCH_STATUS = 0
>   BEGIN
> Print @SQlStmt
> EXECUTE @Rtncde = sp_executesql @stmt = @SQLStmt
> UPDATE @tblGEN
> SET RtnCde = @RtnCde,
>     Cnt    = @@Rowcount
>          WHERE current of SQLCSR
> Fetch NEXT From SQLCSR into @Relation, @Attribute, @SQLStmt
>   END
>
>
> CLOSE SQLCsr
> DEALLOCATE SQLCsr
>
>
>
>