Syntax error with table name in dynamic SQL

Question

I wrote a stored procedure in SQL Server to check if data is present in a few tables and if not then the query will fail but if the data is present then it will insert some data in table 3. But I see I'm getting an error with the DB name that I am passing through variable. Below is the the error message.

Msg 208, Level 16, State 1, Line 65
Invalid object name 'EMR-Integration-DEV.dbo.EMR_ADF_Variables_test'

Here is my stored procedure:

CREATE PROCEDURE InsertData
    @Client_Id INT,
    @EMR_Id INT,
    @Client_Name NVARCHAR(255),
    @EMR_Name NVARCHAR(255),
    @DB_Name NVARCHAR(255)
AS
BEGIN
    DECLARE @Full_Table_name NVARCHAR(255);
    SET @Full_Table_name = @DB_Name + '.dbo.Variable_Table_Name'; 

    -- Check if values exist in tables in the Reference database
    IF NOT EXISTS (SELECT 1 FROM Dim.Client_table WHERE ClientId = @Client_Id AND StagedName = @Client_Name)
    BEGIN
        PRINT 'Error: Client does not exist.';
        RETURN;
    END

    IF NOT EXISTS (SELECT 1 FROM  [dbo].[EMR] WHERE EMR_Id = @EMR_Id AND EMR_Name = @EMR_Name)
    BEGIN
        PRINT 'Error: EMR does not exist.';
        RETURN;
    END
    
    DECLARE @SqlQuery NVARCHAR(MAX);

    SET @SqlQuery = 
        'IF NOT EXISTS (SELECT 1 FROM ' + QUOTENAME(@Full_Table_name) + ' WHERE Client_Id = ' + CAST(@Client_Id AS NVARCHAR) + ' AND EMR_Id = ' + CAST(@EMR_Id AS NVARCHAR) + ')
        BEGIN
            INSERT INTO ' + QUOTENAME(@Full_Table_name) + ' (Client_Id, EMR_Id, Client_Name, EMR_Name)
            VALUES (' + CAST(@Client_Id AS NVARCHAR) + ', ' + CAST(@EMR_Id AS NVARCHAR) + ', N''' + @Client_Name + ''', N''' + @EMR_Name + ''');
            INSERT INTO ' + QUOTENAME(@Full_Table_name) + ' (Client_Id, EMR_Id, Client_Name, EMR_Name)
            VALUES (' + CAST(@Client_Id AS NVARCHAR) + ', ' + CAST(@EMR_Id AS NVARCHAR) + ', N''' + @Client_Name + ''', N''' + @EMR_Name + ''');
        END';

    EXEC sp_executesql @SqlQuery;

    PRINT 'Data inserted successfully.';
END

Please help me with this.

I tried removing and adding single codes where to get correct DB name but it doesn't work.

Answer 1

You are quoting at the wrong point. QUOTENAME expects a single object or database name, not a three part name.

Instead, quote just the database name (the only dynamic bit) at the top of the procedure.

DECLARE @Full_Table_name NVARCHAR(1000) = QUOTENAME(@DB_Name) + '.dbo.Variable_Table_Name';

Then remove the quoting lower down. Also you should pass through parameters properly. And never use n/varchar without a length.

DECLARE @SqlQuery NVARCHAR(MAX) = '
IF NOT EXISTS (SELECT 1
    FROM ' + @Full_Table_name + '
    WHERE Client_Id = @Client_Id
      AND EMR_Id = @EMR_Id
)
BEGIN
    INSERT INTO ' @Full_Table_name + '
        (Client_Id, EMR_Id, Client_Name, EMR_Name)
    VALUES
        (@Client_Id, @EMR_Id, @Client_Name, @EMR_Name),
        (@Client_Id, @EMR_Id, @Client_Name, @EMR_Name);
END;
';

EXEC sp_executesql @SqlQuery
  N'@Client_Id INT,
    @EMR_Id INT,
    @Client_Name NVARCHAR(255),
    @EMR_Name NVARCHAR(255)',
  @Client_Id = @Client_Id,
  @EMR_Id = @EMR_Id,
  @Client_Name = @Client_Name.
  @EMR_Name = @EMR_Name;

Another option, given that only the database name is dynamic, is to use a dynamic procedure name.

DECLARE @proc nvarchar(1000) = QUOTENAME(@DB_Name) + '.sys.sp_executesql';

DECLARE @SqlQuery NVARCHAR(MAX) = '
IF NOT EXISTS (SELECT 1
    FROM dbo.Variable_Table_Name
    WHERE Client_Id = @Client_Id
      AND EMR_Id = @EMR_Id
)
BEGIN
    INSERT INTO dbo.Variable_Table_Name
        (Client_Id, EMR_Id, Client_Name, EMR_Name)
    VALUES
        (@Client_Id, @EMR_Id, @Client_Name, @EMR_Name),
        (@Client_Id, @EMR_Id, @Client_Name, @EMR_Name);
END;
';

EXEC @proc @SqlQuery
  N'@Client_Id INT,
    @EMR_Id INT,
    @Client_Name NVARCHAR(255),
    @EMR_Name NVARCHAR(255)',
  @Client_Id = @Client_Id,
  @EMR_Id = @EMR_Id,
  @Client_Name = @Client_Name.
  @EMR_Name = @EMR_Name;