CODEWITHSUNDEEP
sql-serverstored-proceduresdynamic-sql
sanjay
sanjay

Reputation: 3

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

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

Upvotes: 0

Views: 228

Answers (2)

Brian Stork
Brian Stork

Reputation: 980

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;

Upvotes: 0

Stuck at 1337
Stuck at 1337

Reputation: 2084

You're getting 1 for every query because:

SELECT COUNT(*) FROM sys.all_columns;
SELECT @@ROWCOUNT; -- always 1!

Assuming the query pattern is always...

SELECT {something like a count} and only one row

...you can do this:

-- somewhere way above:

DECLARE @sql nvarchar(max);

-- then inside the cursor:

SET @sql = N'WITH cte(rcount) AS ('
  + @query + ') SELECT @QueryID, rcount FROM cte;';

INSERT dbo.ResultsTable(QueryID, Result)
  EXEC sys.sp_executesql @sql, N'@QueryID int', @QueryID;

Upvotes: 1

Related Questions