I would like to output the result of the dynamic SQL into a variable called @Count but not sure what the syntax or even the code should like to accomplish this.
The code looks as follows:
declare @tab nvarchar(255) = 'Person.person'
declare @Count int
declare @SQL nvarchar(max) = 'select count(*) from '+ @tab
exec(@SQl)
select @Count
thank you
Here's another way to do it that also safely addresses the SQL Injection isuues:
/* Counts the number of rows from any non-system Table, *SAFELY* */
-- The table name passed
DECLARE @PassedTableName as NVarchar(255) = 'Person.Person';
-- Make sure this isn't a SQL Injection attempt
DECLARE @ActualTableName AS NVarchar(255)
SELECT @ActualTableName = TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = PARSENAME(@PassedTableName,1)
AND TABLE_SCHEMA = PARSENAME(@PassedTableName,2)
-- make a temp table to hold the results
CREATE TABLE #tmp( cnt INT );
-- create the dynamic SQL
DECLARE @sql AS NVARCHAR(MAX)
SELECT @sql = 'SELECT COUNT(*) FROM ' + @ActualTableName + ';'
-- execute it and store the output into the temp table
INSERT INTO #tmp( cnt )
EXEC(@SQL);
-- Now, finally, we can get it into a local variable
DECLARE @result AS INT;
SELECT @result = cnt FROM #tmp;
You can utilize sp_executesql to execute your count() query, and output it @Count.
Try this:
-- Set the table to count from
declare @tab nvarchar(255) = 'Person.person'
-- Assign the SQL query
declare @SQL nvarchar(255) = N'SELECT count(*) FROM ' + @tab
-- Pepare for sp_executesql
declare @Count int
declare @Params nvarchar(100) = N'@Count int output'
-- Set the count to @Count
exec sp_executesql @SQL, @Params, @Count=@Count output
-- Output @Count
select @Count
One last thing: Person.person looks like you might be trying to reference a person column from a Person table. But the above query is a literal representation of what it looks like you're trying to achieve in your question.
The below question is pretty much identical to what you are asking here.
sp_executeSql with output parameter
DECLARE @retval int
DECLARE @sSQL nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @tablename nvarchar(50)
SELECT @tablename = N'products'
SELECT @sSQL = N'SELECT @retvalOUT = MAX(ID) FROM ' + @tablename;
SET @ParmDefinition = N'@retvalOUT int OUTPUT';
EXEC sp_executesql @sSQL, @ParmDefinition, @retvalOUT=@retval OUTPUT;
SELECT @retval;
