title: Compare two table using dynamic database
body: <p>I have 2 table (same name) on 2 different database</p>
  
  <p><img src="https://i.stack.imgur.com/tR3pB.png" alt="MY TABLES"></p>
  
  <p>I want compare these table, this is my procedure</p>
  
  <pre><code>ALTER PROCEDURE COUNTCOLUMN 
      @TABLENAME NVARCHAR(MAX), 
      @DATABASENAME1 NVARCHAR(MAX),
      @DATABASENAME2 NVARCHAR(MAX)
  AS  
  BEGIN
      DECLARE @COLNAME   NVARCHAR(MAX)
      DECLARE @ROWCOUNT  INT
      DECLARE @ROWCOUNT2 INT
      DECLARE @NUMB1     INT 
      DECLARE @NUMB2     INT
  
      DECLARE @SQLQUERY  NVARCHAR(MAX)
      DECLARE @SQLQUERY2 NVARCHAR(MAX)
      DECLARE @SQLQUERY3 NVARCHAR(MAX)
      DECLARE @SQLQUERY4 NVARCHAR(MAX)
      DECLARE @SQLQUERY5 NVARCHAR(MAX)
      DECLARE @SQLQUERY6 NVARCHAR(MAX)
  
      SET @SQLQUERY  = 'SELECT @NUMBER = COUNT(*) FROM information_schema.columns  WHERE table_name =  '+N'@TESTTB'+''
      SET @SQLQUERY2  = 'USE ' + @DATABASENAME1 
      SET @SQLQUERY3  = 'USE ' + @DATABASENAME2
  
      EXECUTE sp_executesql @SQLQUERY2
  
      EXECUTE sp_executesql @SQLQUERY, N'@TESTTB NVARCHAR(MAX), @NUMBER INT OUTPUT',@TESTTB = @TABLENAME ,@NUMBER = @NUMB1 OUTPUT
  
      EXECUTE sp_executesql @SQLQUERY3
      EXECUTE sp_executesql @SQLQUERY, N'@TESTTB NVARCHAR(MAX), @NUMBER INT OUTPUT',@TESTTB = @TABLENAME ,@NUMBER = @NUMB2 OUTPUT
  
      IF(@NUMB1 &lt;= @NUMB2)
      BEGIN
          DECLARE @NUMB INT
          SET @NUMB = 1
  
          WHILE (@NUMB &lt;= @NUMB1)
          BEGIN
              IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES  
                          WHERE TABLE_NAME = 'TEMP1'))
              BEGIN
                  DROP TABLE TEMP1
              END
  
              IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES  
                          WHERE TABLE_NAME = 'TEMP2'))
              BEGIN
                  DROP TABLE TEMP2
              END
              ELSE
              BEGIN
                  SET @SQLQUERY4 = 'SELECT @COL_NAME = COL_NAME(OBJECT_ID(@TABLENAME), @NUMB)'            
  
                  EXECUTE sp_executesql @SQLQUERY2
                  EXECUTE sp_executesql @SQLQUERY4, N'@TABLENAME NVARCHAR(MAX),@NUMB INT, @COL_NAME NVARCHAR(MAX) OUTPUT', @TABLENAME = @TABLENAME,@NUMB=@NUMB, @COL_NAME = @COLNAME OUTPUT
  
                  SET @SQLQUERY5 = 'SELECT '+ @COLNAME + ' INTO TEST_DB..TEMP1 FROM ' + @TABLENAME 
  
                  EXECUTE sp_executesql @SQLQUERY2
                  EXECUTE sp_executesql @SQLQUERY5
  
                  EXECUTE sp_executesql @SQLQUERY3                   
                  EXECUTE sp_executesql @SQLQUERY4, N'@TABLENAME NVARCHAR(MAX),@NUMB INT, @COL_NAME NVARCHAR(MAX) OUTPUT', @TABLENAME = @TABLENAME,@NUMB=@NUMB, @COL_NAME = @COLNAME OUTPUT
  
                  SET @SQLQUERY6 = 'SELECT ' + @COLNAME +' INTO TEST_DB..TEMP2 FROM '+ @TABLENAME 
  
                  EXECUTE sp_executesql @SQLQUERY3
                  EXECUTE sp_executesql @SQLQUERY6
  
                  SELECT @ROWCOUNT  = COUNT(*) FROM TEMP1 
                  SELECT @ROWCOUNT2 = COUNT(*) FROM TEMP2
  
                  IF(@ROWCOUNT != @ROWCOUNT2)
                  BEGIN
                      PRINT N'NUMBER OF ROWS ARE NOT EQUAL';
                      BREAK
                  END
                  ELSE IF (@NUMB &gt; @NUMB1)
                      BREAK
                  ELSE
                  BEGIN        
                      SELECT * FROM TEST_DB..TEMP1 
                      EXCEPT 
                      SELECT * FROM TEST_DB..TEMP2
  
                      SET @NUMB = @NUMB + 1;
                      CONTINUE       
                  END
             END
       END
  END
  END
  </code></pre>
  
  <p>It should be like this TEMP1 &lt;--TEST_DB..TESTTABLE, TEMP2 &lt;--TEST_DB2..TESTTABLE</p>
  
  <p>But TEMP1 , TEMP2 always have the same values as TESTTABLE from the first database (TEST_DB )</p>
  
  <p><img src="https://i.stack.imgur.com/2oCUL.png" alt="MY RESULT"></p>
  
  <p><img src="https://i.stack.imgur.com/psNQN.png" alt="EXPECTED RESULT"></p>
  
  <p>It seem like I can't switch between two databases in procedure so I try to run it separate but still not work</p>