1

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

RBarryYoung
  • 55,398
  • 14
  • 96
  • 137
abs786123
  • 581
  • 2
  • 11
  • 24

3 Answers3

3

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;
RBarryYoung
  • 55,398
  • 14
  • 96
  • 137
  • I'm unable to test this, but are you sure you can insert the results of an exec into another table? I tested it on Sybase (closest I have to SQLServer at the moment) and I cannot insert the dynamic sql result into a table. – RToyo Jul 07 '17 at 19:57
  • @RobbieToyota Yes, I am sure. I do it all the time, plus I tested the code before I posted it. – RBarryYoung Jul 07 '17 at 20:19
  • 1
    @RobbieToyota Here's a link to the doc with example. https://learn.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql#OtherTables – RBarryYoung Jul 07 '17 at 20:24
2

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.

RToyo
  • 2,877
  • 1
  • 15
  • 22
  • 1
    Hmmm. Did you actually try this? – John Cappelletti Jul 07 '17 at 19:22
  • 1
    Dynamic SQL context cannot see nor modify variables in an outer context. – RBarryYoung Jul 07 '17 at 19:22
  • Great point; that thought about variable scope hadn't crossed my mind. @Jesse just posted an answer that I was about to mention here. I'll modify this to use sp_executeSql shortly. – RToyo Jul 07 '17 at 19:25
  • HI, I have tried it, but i am getting this error message, Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'SELECT'. (1 row(s) affected) – abs786123 Jul 07 '17 at 19:27
  • I am getting this with the answer posted by @Robbie – abs786123 Jul 07 '17 at 19:27
  • @abs786123 That was my bad. I thought it would be a quick and simple answer about variables and dynamic SQL; I don't even have access to an SQLServer environment at the moment to test this (but since I've posted an answer, I'm trying to follow through on it). I've updated the answer to use sp_executesql, which will output the count to `@Count`. – RToyo Jul 07 '17 at 19:36
  • Hi Rob, getting a diffrent error message this time. Msg 214, Level 16, State 3, Procedure sp_executesql, Line 1 Procedure expects parameter '@params' of type 'ntext/nchar/nvarchar'. – abs786123 Jul 07 '17 at 19:40
  • I tried changing the int to nvarchar and also tried putting N' at the beginning of the @SQL variable, but no without success – abs786123 Jul 07 '17 at 19:41
  • Thanks Rob for your effort – abs786123 Jul 07 '17 at 19:44
  • @abs786123 No worries; sorry I jumped to giving you a bad answer while trying to work from memory. I've updated the answer once more. Just for my own curiosity, do you mind trying to see if my updated answer works? I see that you've accepted Jesse's answer, and I'll be deleting this answer if it's still broken. (No point in keeping broken answers on SO) – RToyo Jul 07 '17 at 19:46
  • 1
    Thanks Again Rob, that really helps a novice like me so I have accepted your answer as its in the context of my question – abs786123 Jul 07 '17 at 19:54
2

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;
Jesse
  • 865
  • 10
  • 18
  • what is @retvalOUT, it is working without declaring it.. Please explain.. – som_1522 Apr 08 '23 at 18:50
  • @som_1522 It has been a bit on this question, fun to see my old answer. retvalOUT is declared in the parameter definition for sp_executesql. Check out the "SET @ParmDefinition" – Jesse Apr 10 '23 at 19:35