asked on

How to assign count(*) to a variable using a dynamically generated statement excecuted by sp_executesql

I am trying to perform a select count(*) against a set of tables within a database. The tablenames and database names are dynamic. I am trying to assign the count to a variable for inserting into a second table that holds auditing information.

I have tried 2 different approaches thus far and receive different error messages syntax errors:

Approach 1:
declare @RowCount bigint
declare @SourceTable varchar(100)
declare @SourceDatabase varchar(100)
declare @SQLStatement varchar(4000)

set @SourceTable = 'DLctblSource'
set @SourceDatabase = 'discode_Db'
set @RowCount = 0

set @SQLStatement = 'select count(*) from '+ @SourceDatabase +'.dbo.'+ @SourceTable + ''
print @SQLStatement
execute master.dbo.sp_executesql @SQLStatement
Error Message: Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'

Approach 2:
declare @RowCount bigint
declare @SourceTable varchar(100)
declare @SourceDatabase varchar(100)
declare @SQLStatement varchar(4000)
set @SourceTable = 'DLctblSource'
set @SourceDatabase = 'discode_Db'
set @RowCount = 0
select @RowCount = count(*) from '+ @SourceDatabase +'.dbo.'+ @SourceTable + '
print @SQLStatement
Error Message: Line 11: Incorrect syntax near '+ @SourceDatabase +'.

I am then trying to insert either the @RowCount value into the audit table or am inserting the results of the select count(*) by

using set @SQLStatement = 'insert into Audit_tbl(tblname,rec_cnt) select @tblname,count(*) from '+ @SourceDatabase +'.dbo.'+ @SourceTable +)

I am encountering syntax errors with both approaches- getting late in PM on East Coast

Scott Pletcher

Method 1 should be used, with some adjustments :-) :

declare @SQLStatement Nvarchar(4000)

set @SQLStatement = N'select @RowCount = count(*) from '+ @SourceDatabase +N'.dbo.'+ @SourceTable + N''
print @SQLStatement
execute master.dbo.sp_executesql @SQLStatement, '@RowCount BIGINT OUTPUT', @RowCount OUTPUT

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mikevs

ASKER

declare @SQLStatement Nvarchar(4000)
declare @SourceTable varchar(30)
declare @SourceDatabase varchar(30)
declare @RowCount bigint
set @SourceTable = 'DLctblSource'
set @SourceDatabase = 'DiscoveryStage_Db'
set @RowCount = 0
set @SQLStatement = N'select @RowCount = count(*) from '+ @SourceDatabase +N'.dbo.'+ @SourceTable + N''
print @SQLStatement
execute master.dbo.sp_executesql @SQLStatement, '@RowCount BIGINT OUTPUT', @RowCount OUTPUT

Result:
/*-----------------------------
declare @SQLStatement Nvarchar(4000)
declare @SourceTable varchar(30)
declare @SourceDatabase varchar(30)
-----------------------------*/
select @RowCount = count(*) from DiscoveryStage_Db.dbo.DLctblSource
Server: Msg 214, Level 16, State 3, Procedure sp_executesql, Line 10
Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'.

paelo

Is DLctblSource a table or a procedure?

-Paul.

jchopde

As ScottPletcher wrote, you need to change this line

execute master.dbo.sp_executesql @SQLStatement, '@RowCount BIGINT OUTPUT', @RowCount OUTPUT

to

execute master.dbo.sp_executesql @SQLStatement, N'@RowCount BIGINT OUTPUT', @RowCount OUTPUT

davehilditch

Change this line:

set @SQLStatement = 'select count(*) from '+ @SourceDatabase +'.dbo.'+ @SourceTable + ''

to

set @SQLStatement = 'select count(*) from '+ @SourceDatabase + '.dbo.' + @SourceTable -- <-- why were you adding empty quotes here?

then instead of

execute master.dbo.sp_executesql @SQLStatement

just use:

exec(@SQLStatement)

Technically, exec not quite as fast as sp_executesql, but we are talking milliseconds, and it will work with code above.

Dave Hilditch.

mikevs

ASKER

DLctblSource is a table name. I have a variation of the procedure that goes against SYSINDEXES, but i am not guaranteed that the statistics are always up to date with the execution of this procedure.

Scott Pletcher

Unfortunately EXEC() will not directly return a query value to the calling code; you have to sp_executeSQL.