How to Capture the result of the dynamic query and store into another table

Question

I have table.

QueryID	Query
1	select count(*) from emp name is not null
2	select count(*) from dept where id is not null

Now I am writing a stored procedure to run the query and store the results in target table as shown here:

QueryID	Result
1	2300
2	4500

But I am getting the result as below table by using this stored procedure:

QueryID	Result
1	1
2	1

Code:

CREATE PROCEDURE ExecuteQueriesAndStoreResults
AS
BEGIN
    DECLARE @query NVARCHAR(255);
    DECLARE @queryID NVARCHAR(255);

    -- Declare variables for query results
    DECLARE @queryResult NVARCHAR(255);

    -- Declare a cursor to loop through TableA
    DECLARE @curQuery CURSOR;

    SET @curQuery = CURSOR FOR 
       SELECT Query, QueryID 
       FROM TableA;

    OPEN @curQuery;

    FETCH NEXT FROM @curQuery INTO @query, @queryID;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Execute the dynamic query
        EXEC(@query);

        -- Capture the result of the dynamic query
        SET @queryResult = @@ROWCOUNT; -- Store the result as needed
       
        -- Insert the result along with the QueryID into ResultsTable
        INSERT INTO ResultsTable (QueryID, Result)
        VALUES (@queryID, @queryResult);

        FETCH NEXT FROM @curQuery INTO @query, @queryID;
    END;

    CLOSE @curQuery;
    DEALLOCATE @curQuery;
END;

QueryID	Result
1	2300
2	4500

Answer 1

Use sp_executesql instead of exec to execute dynamic SQL. It allows for parameters to be passed back and forth.

declare @stmt nvarchar(max);
declare @params nvarchar(max);
declare @rowcount int;
set @stmt = N'select @rowcount = count(*) from dbo.YOURTABLE where ID is not null;'
set @params = N'@rowcount int output';
exec sp_executesql @stmt = @stmt
   , @params = @params
   , @rowcount = @rowcount output;
select @rowcount;