Dynamic SQL is a tool that many of us may end up using in the future, or have in the past. Often, however, it's use can frowned upon because it often can be a sign that what you have really is a design problem, or what is known as an "XY Problem". Most of the questions I do see are an indication of this, such as "I need a Procedure that accepts a table name as a parameter for an INSERT." These sorts of requirements are often clear examples, as this likely means you are using objects names to infer details about the data; perhaps the date of the data or the company when you use different schemas. Although I don't condone these types of design, some of the below will be using solutions which may well promote that design. The point of this article is to show how you would write dynamic SQL and is not aimed to promote designs where they exist.
Injecting the object name
The most basic form of dynamic SQL is replacing the name of an object. This requires using a variable (or parameter) with the name of the object and injecting that value into a string value for execution. The most important part is ensuring that you inject that value securely; I cannot stress that more. Not securely injecting your values into a dynamic SQL opens a huge security on your code that will be exploited one day.
The easiest way to safely inject an object's name is to use QUOTENAME. This accepts 2 parameters, the first is the value to quote, and accepts a sysname, while the latter is a optional parameter that can be used to define the delimit identifier if you want one that isn't the default (brackets ([])). So using the function is as simple as QUOTENAME(@TableName). Note that syntax like N'[' + @TableName + N']' is not not safe from injection attacks. The right bracket (]) can easily be closed by someone malicious, just like a single quote (') can be in a badly "parametrised" statement.
Other than that, all you need do if define your parameters/variables and a variable to store the string. I do, however, suggest using sys.sp_executesql not EXEC(@SQL). The reason for this is that if you do use the latter parametrisation isn't possible. Even if you don't need parameters this time doesn't mean you shouldn't plan for it.
Example 1: Basic Dynamic Statement
DECLARE @SchemaName sysname = N'dbo', @TableName sysname; --These would be your parameters normally. I assign @SchemaName as it would likely default as one
SET @TableName = N'MyTable';
DECLARE @SQL nvarchar(MAX);
SET @SQL = N'SELECT * FROM ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N';';
--PRINT @SQL; --Always your best friend.
EXEC sys.sp_executesql @SQL;Parametrising your Dynamic Statement
Obviously, as I alluded to, your statement may well need to have parameters passed to it as well. One mistake people make frequently is injecting these values into the statement. This isn't the right way to do this. Instead sys.sp_executesql accepts additional parameters so that you pass these parameters. The second parameter is used to define the parameters, and then any further to pass values for those values.
The way you define the parameters for sys.sp_executesql is the same as you would in "normal" SQL, in a sense, in the format @{Variable Name} {VariableType}, apart from that they are enclosed within a literal nvarchar and are comma separated in there. For example N'@Name nvarchar(50), @Price decimal(8,2)'. After the second parameter (for sys.sp_executesql), you pass each parameter as you would for a normal procedure. For the prior example that could be something like @Name = @ProductName, @Price = @ProductPrice.
Example 2: Parameterised Dynamic Statement
DECLARE @SchemaName sysname = N'dbo', @TableName sysname,
@Date date, @ID int;
SET @TableName = N'MyTable';
SET @Date = '20201027';
SET @ID = 1;
DECLARE @SQL nvarchar(MAX);
SET @SQL = N'SELECT * FROM ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' WHERE ID = @ID AND MyDate = @Date;';
--PRINT @SQL; --Always your best friend.
EXEC sys.sp_executesql @SQL, N'@Date date, @ID int',@Date, @ID;Injecting values other than objects
As mentioned earlier QUOTENAME accepts a second parameter, which can be use to quote the value with characters other than brackets ([]). In addition to brackets, you can also use single quotation marks ('), double quotation marks ("), parenthesis (()), greater than or less than signs (><), braces ({}), and backticks (`). If you are therefore injecting a value to should be wrapped in something else, and (importantly) the value has no more than 128 characters, you have use QUOTENAME to quote that value. Otherwise, if you do need to pass a value that over 128 characters long, then you would need to use REPLACE to appropriate escape the character. No matter the character, to escape it you "double it up", for example REPLACE(@MyVariable, '"','""').
Example 3: Injecting strings
Note: that this does not use the proper parametrisation as shown in Example 2. This is not advised and is for demonstration purposes only. Also the value of both @TableName and @CustomerName are prefixed with an N in the dynamic statement, as otherwise they would be interpreted as a varchar not an nvarchar.
DECLARE @SchemaName sysname = N'dbo', @TableName sysname,
@CustomerName nvarchar(150);
SET @TableName = N'YourTable';
SET @CustomerName = N'Thom A';
DECLARE @SQL nvarchar(MAX);
SET @SQL = N'SELECT N' + QUOTENAME(@TableName,'''') + N'AS TableName, * FROM ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' WHERE CustomerName = N' + REPLACE(@CustomerName,'''','''''') + N';';
--PRINT @SQL; --Always your best friend.
EXEC sys.sp_executesql @SQL;