error using parameter for table name in dynamic sql

• scotsditch

  Ten Centuries

  Points: 1020

  More actions

  September 28, 2018 at 4:41 pm

  #368900

  The code below isn't complete. I didn't include the creation of the temp table. My problem is that I'm using parameters to specify the database names, then I'm using another parameter for the table name inside a while loop with dynamic sql. I want to loop through the list of tables the databases have in common and display the count of records that are only in the table in the first database. The problem I'm running in to is using a parameter for the combination of database and table name. The database names will stay the same, the table names will change as it goes through the while loop. Every way I try to concatenate them throws an error. Any tips are greatly appreciated.

  Code:

  Declare @DB1 Varchar(MAX)
Declare @DB2 Varchar(MAX)

  --testing code

  SET @DB1='sfData'
SET @DB2='sfSync'

  /*omitted code***/

  exec(
'
Declare @TableName Varchar(MAX);

  while (select count(NameSchema) from #TempCommonMatchFormatted)>0
begin
        select top 1 @TableName=NameSchema from #TempCommonMatchFormatted;
        
        select @TableName as UpdateTable, count(*) as UpdateCnt from(
                select * from '+@DB1+'@TableName
                except
                select * from '+@DB2+'@TableName
                )a
        
        
        
        
        Delete from #TempCommonMatchFormatted where NameSchema = @TableName;
    End'
    )


  Error:

  Invalid object name 'sfData@TableName'.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  September 29, 2018 at 11:41 pm

  #2007433

  scotsditch - Friday, September 28, 2018 4:41 PM

  The code below isn't complete. I didn't include the creation of the temp table. My problem is that I'm using parameters to specify the database names, then I'm using another parameter for the table name inside a while loop with dynamic sql. I want to loop through the list of tables the databases have in common and display the count of records that are only in the table in the first database. The problem I'm running in to is using a parameter for the combination of database and table name. The database names will stay the same, the table names will change as it goes through the while loop. Every way I try to concatenate them throws an error. Any tips are greatly appreciated.

  Code:

  Declare @DB1 Varchar(MAX)
Declare @DB2 Varchar(MAX)

  --testing code

  SET @DB1='sfData'
SET @DB2='sfSync'

  /*omitted code***/

  exec(
'
Declare @TableName Varchar(MAX);

  while (select count(NameSchema) from #TempCommonMatchFormatted)>0
begin
        select top 1 @TableName=NameSchema from #TempCommonMatchFormatted;
        
        select @TableName as UpdateTable, count(*) as UpdateCnt from(
                select * from '+@DB1+'@TableName
                except
                select * from '+@DB2+'@TableName
                )a
        
        
        
        
        Delete from #TempCommonMatchFormatted where NameSchema = @TableName;
    End'
    )


  Error:

  Invalid object name 'sfData@TableName'.

  First, you can't use variables the way you are attempting in your code.

  Second, you must have heard that you should avoid using cursors at all costs.  Cursors are a tool.  If used correctly they are good, if used incorrectly they are evil.  This is a perfect place where a properly configured fire hose cursor will out perform the #temporary table method you are trying to use.

  Third, if you had provided your complete code I could have provided you with a more complete solution instead of the snippet I put together:
  
declare @SQLCmdTemplate nvarchar(MAX) = N'
select
  ''~TableName~'' as UpdateTable
  , count(*)
from
  (
  select * from ~DataBaseName1~.~SchemaName~.~TableName~
  except
  select * from ~DataBaseName2~.~SchemaName~.~TableName~
  ) as dt
';
declare @SQLCmd nvarchar(MAX)
        , @DB1 nvarchar(128)
        , @DB2 nvarchar(128);-- Missing fire hose cursor code and variable declarations to support the code--open ,cursor name>;while 1 = 1
begin
  --fetch next from <cursor name> into <unknown variable names>;
  if @@fetch_status <> 0
    break -- exit the loop as no more data to process
 
  set @SQLCmd = replace(replace(replace(replace(@SQLCmdTemplate,'~TableName~',quotename(@TableName)),'~SchemaName~',quotename(@SchemaName)),'~DatabaseName1~',quotename(@DB1)),'~DatabaseName1~',quotename(@DB2));
  print @SQLCmd;
  exec sys.sp_executesql @stmt = @SQLCmd;
end--close <cursor name>;
--deallocate <cursor name>;