Count Number of records of a Dynamic build query into a variable

Question

I am building a query in which number of columns selected is not fixed so i am generating my query runtime and executing it in a stored procedure. But i am stuck now because i want to perform some operation from my query according to the number of records but i don't know how to get count of numbers of record from a Dynamic query where my query is like

DECLARE @RowCount AS numeric(18,0)
DECLARE @SQL AS VARCHAR(MAX)
Set @SQL ='Select Count(*) From ((Select Col1,Col2,Col3,Col4 From Table1)) as rowcount'
Exec(@SQL)
Set @RowCount  = ????..

Can you please tell me how i got count of records from a dynaimc query

Answer 1

Count of number of records is independent of columns in SELECT list unless some condition is provided in WHERE clause.

Just do this

DECLARE @SQL AS VARCHAR(MAX)
Set @SQL ='Select COUNT(*) From Table1'
Exec(@SQL)

So if you want to store rowcount value returned use a table variable and insert into it

  DECLARE @SQL AS VARCHAR(MAX)
  DECLARE @rowCountTable TABLE(row_count INT)

  Set @SQL ='Select COUNT(*) From Table1'

  INSERT INTO @rowCountTable 
  Exec(@SQL)


  SELECT * FROM @rowCountTable 

You can also retrieve value using OUTPUT variable

DECLARE @retCount int   
DECLARE @SQL AS NVARCHAR(MAX)
DECLARE @outPut NVARCHAR(50);


SET @SQL = N'Select @retvalOUT=COUNT(*) From Table1'  
SET @outPut = N'@retvalOUT int OUTPUT';

EXEC sp_executesql @SQL, @outPut, @retvalOUT=@retCount OUTPUT;

SELECT @retCount;