How can I put a succeeding values into another table while inside of a cursor?-CodePudding

How can I achieve this , I wanted to collect all of the counts in one place.




-- 1 - Declare Variables
-- * UPDATE WITH YOUR SPECIFIC CODE HERE *
DECLARE @table_name VARCHAR(50) -- table name 
DECLARE @inst VARCHAR(50)
DECLARE @sql NVARCHAR(100)
DECLARE @ctr INT

-- Initialize Variables
-- * UPDATE WITH YOUR SPECIFIC CODE HERE *
SET @table_name = N'tblTranslate' 



-- 2 - Declare Cursor
DECLARE db_cursor CURSOR FOR 
-- Populate the cursor with your logic
-- * UPDATE WITH YOUR SPECIFIC CODE HERE *
SELECT idauctionsite FROM #pastauction 

-- Open the Cursor
OPEN db_cursor

-- 3 - Fetch the next record from the cursor
FETCH NEXT FROM db_cursor INTO @inst  

-- Set the status for the cursor
WHILE @@FETCH_STATUS = 0  
 
BEGIN  
    -- 4 - Begin the custom business logic
    -- * UPDATE WITH YOUR SPECIFIC CODE HERE *
    SET @sql = N'SELECT COUNT(*) as cout FROM'  ' '   'dbo.'  CAST(CONCAT(@table_name,@inst) AS VARCHAR(50))
    EXEC sp_executesql @sql
    


    -- 5 - Fetch the next record from the cursor
    FETCH NEXT FROM db_cursor INTO @inst 
END 

-- 6 - Close the cursor
CLOSE db_cursor  

-- 7 - Deallocate the cursor
DEALLOCATE db_cursor

This script returns multiple results row/grid.

I wanted to put all the cout in something like temporary table but I'm currently stuck because I use dynamic query.

CodePudding user response：

You don't need a cursor, nor to insert the data into a temporary table. You can just create a UNION ALL style query dynamically, then execute it.

-- * UPDATE WITH YOUR SPECIFIC CODE HERE *
DECLARE @table_name nvarchar(50) = N'tblTranslate' 

DECLARE @sql nvarchar(max) = (
    SELECT STRING_AGG(CAST('
SELECT '   QUOTENAME(pa.idauctionsite, '''')   ' AS idauctionsite, COUNT(*) as cout
FROM dbo.'   QUOTENAME(@table_name   pa.idauctionsite)
      AS nvarchar(max)), '
UNION ALL'
      )
    FROM #pastauction pa
);

PRINT @sql; -- for testing

EXEC sp_executesql @sql;

CodePudding user response：

@Larnu is suggesting something like this:

DECLARE @results table(inst varchar(50), cout int)
BEGIN  
    -- 4 - Begin the custom business logic
    -- * UPDATE WITH YOUR SPECIFIC CODE HERE *
    SET @sql = N'SELECT ' @inst ' as inst, COUNT(*) as cout FROM'  ' '   'dbo.'  CAST(CONCAT(@table_name,@inst) AS VARCHAR(50))
    insert into @results
    EXEC sp_executesql @sql
    


    -- 5 - Fetch the next record from the cursor
    FETCH NEXT FROM db_cursor INTO @inst 
END 

select * from @results