title: T SQL Cursor Returning Empty Table
body: <p>I am building a cursor in T-SQL that will loop through each database and set variables that will then be placed into a table. The query completes successfully but the <code>#SaaS</code> table is empty. Printing the SQL shows that the correct database is being used depending on the select in the variable assignment. The cursor does fetch two columns from the <code>#dbNames</code> table as I use different databases depending on the variable. When I look at the <code>#dbNames</code> table the columns do contain nulls since the query spans multiple instances SQL servers so for example the <code>#dbNames</code> table looks like this:</p>
  
  <pre><code>ServerName | dbName         | dbName2
  -------------------------------------
  sql.inst1  | wfa00Reporting | null
  sql.inst1  | null           | wfa00System
  sql.inst1  | wfa01Reporting | null
  sql.inst1  | null           | wfa01System
  sql.inst2  | wfa02Reporting | null
  sql.inst2  | null           | wfa02System
  </code></pre>
  
  <p>I am not sure what I am missing here since there is no error. Why is the <code>#SaaS</code> table empty upon completion of query? </p>
  
  <p>SQL:</p>
  
  <pre><code>/* start */
  
  begin try
  DROP TABLE #SaaS
  end try
  begin catch
  end catch
  begin try
  DROP TABLE #dbNames
  end try
  begin catch
  end catch
  DECLARE
           @prefix nvarchar(50) =  'WFA'
  
  /**
  * Create 2 temp tables for processing
  */
  CREATE TABLE #dbNames (dbName NVARCHAR(255)
                          , dbName2 NVARCHAR(255))
  
  CREATE TABLE #SaaS (dbName NVARCHAR(255)
                      , varA NVARCHAR(255)
                      , varB NVARCHAR(255))
  
  /**
  * Get the databases - all for now
  */
  INSERT INTO #dbNames(dbName, dbName2)
  SELECT CASE WHEN [name] LIKE '%Reporting' THEN [name] END as dbName
          , CASE WHEN [name] LIKE '%System' THEN [name] END as dbName2
  FROM SYS.DATABASES 
  WHERE State = 0 
  
  /**
  * A few variables for the Database list cursor
  */
  DECLARE @dbName NVARCHAR(255)
        , @dbName2 NVARCHAR(255)
        , @sql NVARCHAR(MAX)
  
  /**
  * get the values, only from ACA instances
  */
  DECLARE EachDatabase CURSOR FOR 
   SELECT distinct [dbName],
          [dbName2]
   FROM #dbNames
  
  OPEN EachDatabase 
  FETCH NEXT FROM EachDatabase INTO @dbName, @dbName2
  
  WHILE @@FETCH_STATUS &lt;&gt; -1
  BEGIN
  
    SET @sql = '
                      DECLARE @varA AS nvarchar(255), @varB AS NVARCHAR(255)
  
                      SET @varB = (
                                          SELECT customerName
                                          FROM ' + @dbName2 + '.User.SystemSettings
                                          )
  
                      SET @varA = (
                                      select top 1 varA
                                      from
                                      ' + @dbName + '.eUser.tablecol
                                      WHERE varA &gt;= 1/1/2018
                                      )
  
                      --REPORT STATUS
  
                      insert into #SaaS ( DBName
                                          , varA
                                          , varB
                                          )
                      select ''' + REPLACE(@dbName,'Reporting','') + '''
                              , @varA
                              , @varB
              '
  
    exec(@sql)
  
    FETCH NEXT FROM EachDatabase INTO @dbName, @dbName2
  END
  
  CLOSE EachDatabase
  DEALLOCATE EachDatabase
  -----------------------------------------End Table for SaaS
  
  SELECT *
  from #SaaS
  order by dbName
  
  /* end */
  </code></pre>