How to use & (ampersand) character in table name in dynamic sql?

Question

I am using dynamic SQL to Select from a group of tables where some of the table names contain the '&' character. However once my query hits one of these tables I get the following error:

Incorrect syntax near '&'.

Here is some example code that recreates the error I am getting:

DECLARE @TABLE_NAME AS NVARCHAR(150);
SET @TABLE_NAME = 'A&BTable';

EXEC(
    'SELECT col1, col2, col3
     FROM Warehouse_Repository.dbo.' + @TABLE_NAME
)

How can I alter this query so that I can Select from table names containing '&' using dynamic SQL?

Answer 1

Add a QUOTENAME

    DECLARE @TABLE_NAME AS NVARCHAR(150);
    SET @TABLE_NAME = QUOTENAME('A&BTable');

    EXEC(
        'SELECT col1, col2, col3
         FROM dbo.' + @TABLE_NAME
    )