How to pass a table variable using sp_executesql

Question

I'm trying to create a table using sp_executesql but I keep getting an error that says "Incorrect syntax near '@_TableName'. Any idea what I'm doing wrong here?

Here's the code that I'm using:

DECLARE @SQLString NVARCHAR(MAX), 
        @ParamDefinition NVARCHAR(MAX), 
        @TableName NVARCHAR(MAX);

SET @TableName  = N'[dbo].[MyTable]';

SET @SQLString = N'SELECT * FROM @_TableName;';

SET @ParamDefinition = N'@_TableName NVARCHAR(max)';

EXEC sp_executesql @SQLString, @ParamDefinition, 
                   @_TableName = @TableName;

That yields the error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '@_TableName'.

If I hard code the table name and the column type (I have to do both) then the query works, otherwise I get the incorrect syntax message for both those variables.

In case you're wondering, I want to put this code inside a stored procedure, so that if anyone wants to create or modify a table then they call this stored procedure which can run additional validations.

Answer 1

Figured out the problem.

Apparently sp_executesql expects the parameter definition for a table to be of a table type (see this answer for an example: https://stackoverflow.com/a/4264553/21539).

An easier way to solve this problem was to insert the variables names directly into the SQLStatement string as follows:

DECLARE @SQLString NVARCHAR(MAX), 
        @TableName NVARCHAR(MAX);

SET @TableName  = N'[dbo].[MyTable]';

SET @SQLString = N'SELECT * FROM ' + @TableName + ';';

SET @ParamDefinition = N'@_TableName NVARCHAR(max);

EXEC sp_executesql @SQLString;