In this case I wanted to get the record count of a table and pass this number to another table for reporting services.
But how to get the number of records into a variable?
Well by using executesql procedure.
This procedure is divided into 3 parts:
1. the command with parameter(s)
2. the parameters = contains the values mentioned in the command
3. the values = contains the returning values
see also: h t t p ://www.databasejournal.com/features/mssql/article.php/3286501/T-SQL-Programming-Part-4---Setting-Variables-in-Calling-T-SQL-Code-While-Using-spexecutesql.htm
the code I used was:
declare
@tblName
as varchar
(20
)
set @tblName
= 'tablename'
declare @RECCNT
int
declare @CMD
Nvarchar(100
)
SET @CMD = 'SELECT @RECORDCNT=count(*) from ' + @tablename
print
@CMD
exec
sp_executesql @CMD
,
N'@RECORDCNT int out', @RECCNT out
print
@reccnt
result:
SELECT @RECORDCNT=count(*) from tablename
97954