Exec failed because the name not a valid identifier?

Dynamic SQL: The Do's and Dont's

When to employ dynamic SQL?

While dynamic SQL provides us a flexible foundation for complex queries in SQL Server, its indiscriminate usage may end up unnecessarily overcomplicating your code. If your logic can be expressed without it, then by all means, avoid it like debt.

Safe execution procedure for dynamic SQL

If dynamic SQL is your chosen or only route, then sp_executesql should be your go-to method. It not only reduces SQL injection risk, but also gifts execution plan reuse, yielding performance gains as an added perk.

DECLARE @SQL NVARCHAR(MAX)
SET @SQL = N'SELECT * FROM Table ...'
EXEC sp_executesql @SQL


Unicode strings to the rescue with special characters

To duck data collation or character set mishaps, adorn all SQL string literals with an 'N' prefix. This is especially useful when special characters or non-Latin scripts loom on the data horizon.

Dynamo with Dynamic SQL syntax

For crisp SQL queries with dynamic SQL, object names are best wrapped in brackets using [] or QUOTENAME(). This shields against identifier ambiguity:

-- QUOTENAME() to the rescue:
SET @SQL = N'SELECT * FROM ' + QUOTENAME(@TableName)
-- Brackets do the trick:
SET @SQL = N'SELECT * FROM [' + @TableName + ']'


Performance boosting with best practices

sp_executesql not only beefs up security against SQL injection, but by caching it also boosts execution performance:

DECLARE @SQL NVARCHAR(4000)
SET @SQL = N'SELECT COUNT(*) FROM [dbo].[Users] WHERE Name = @Name'
EXEC sp_executesql @SQL, N'@Name NVARCHAR(50)', N'John Doe'


This, like morning yoga, leads to faster, calmer execution specially with recurrent calls to the same dynamic SQL query.

Scope it out for context

Dynamically executing SQL? Great! But remember, context matters. Check your temporary tables' scope as it can affect visibility:

-- What happens in a stored procedure, stays...
CREATE TABLE #TempTable (...);
EXEC SomeDynamicSQL; -- Oops! #TempTable has just ghosted!
