Looping through the cursor in T-SQL-CodePudding

I am creating a procedure in T-SQL:

CREATE PROCEDURE usp_QC1
AS
BEGIN
    DECLARE @tablename varchar(100);
    DECLARE @sfilename varchar(100);
    DECLARE @rread varchar(100);
    DECLARE @rwrite varchar(100);
    DECLARE @sserver varchar(100);
    DECLARE @dbname varchar(100);

    DECLARE cur_tracking CURSOR FOR
        SELECT
            [TableName],
            sfilename,
            [RecordsRead],
            [RecordsWritten],
            [SQLServer],
            [SQLDatabase]
        FROM 
            [ADataTracker].[dbo].[TrackerLoadLog] 
        WHERE
            LOWER(datagroup) LIKE '%Oracle RMS%'
            AND SUBSTRING(sfilename, 1, 10) = '2022-03-16'

    OPEN cur_tracking;

    FETCH NEXT FROM cur_tracking INTO @tablename, @sfilename, @rread, @rwrite, @sserver, @dbname;

    WHILE @@Fetch_status = 0
    BEGIN
        SELECT 
            sfilename, COUNT(*) 
        FROM
            @sserver   '.dbo.'   @dbname 
        WHERE
            sfilename = @sfilename 
        GROUP BY 
            sfilename

        FETCH NEXT FROM cur_tracking INTO @tablename, @sfilename, @rread, @rwrite, @sserver, @dbname;
    END;

    CLOSE cur_tracking;
    DEALLOCATE cur_tracking;
END

The cursor cur_tracking is holding the data (just showing 1 record)

---------------------------------------------------------------------------------------------------------------------------
TableName       |sfilename                    | RecordsRead |RecordsWritten |SQLServer                      |SQLDatabase  |
History         |2022-03-16\20220316032322.dat| 5819560     |5819560        |a.stg.sql.ccaintranet.com |DataRms_Back |

So the cursor is having all the information of server, Databasename, tablename. I want to go to those each server.db.table and retrieve the information using these cursor elements:

SELECT sfilename, COUNT(*) 
FROM @sserver   '.dbo.'   @dbname 
WHERE sfilename = @sfilename 
GROUP BY sfilename

But I get an error:

invalid column name sfilename
must declare the table variable @sserver

CodePudding user response：

This is never going to work:

select sfilename,count(*) from @sserver '.dbo.' @dbname

You can't parameterize entity names like this (also database comes before schema, and you missed @tablename entirely).

Instead you can do:

DECLARE @sql nvarchar(max), @exec nvarchar(1000);

...
  -- then inside the cursor:
  SET @sql = N'select sfilename = @sfilename,count(*)  
           FROM dbo.'   QUOTENAME(@tablename)
             N' WHERE sfilename = @sfilename;';

  SET @exec = QUOTENAME(@dbname)   N'.sys.sp_executesql';

  EXEC @exec @sql, N'@sfilename varchar(100)', @sfilename;

Note that all of these need to be nvarchar(128), and you can use the same DECLARE for multiple variables to lighten eye strain:

DECLARE @tablename nvarchar(128),
        @sfilename nvarchar(128),
        @dbname    nvarchar(128);

Please review this collection of links.