Solved

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

Posted on 2004-09-22
8
1,158 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:mikevs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 12127495
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
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 12127514
CORRECTIONS:

execute sp_executesql @SQLStatement, N'@RowCount BIGINT OUTPUT', @RowCount OUTPUT
0
 

Author Comment

by:mikevs
ID: 12127562
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'.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 9

Expert Comment

by:paelo
ID: 12127598
Is DLctblSource a table or a procedure?

-Paul.
0
 
LVL 6

Expert Comment

by:jchopde
ID: 12127627
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
0
 
LVL 4

Expert Comment

by:davehilditch
ID: 12127666
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.
0
 

Author Comment

by:mikevs
ID: 12127669
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.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 12127711
Unfortunately EXEC() will not directly return a query value to the calling code; you have to sp_executeSQL.
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question