sp_executesql: what can be parameterized

Is there a definitive list published showing what can and cannot be parameterized in a call to sp_executesql.

For example, predicates can, table names cannot. Number of rows in TOP can:

exec sp_executesql @stmt =
    N'SELECT TOP(@n) a, b
    FROM (VALUES(1,2)) v(a,b)',
    @params = N'@n int',
    @n = 10

MAXDOP cannot:

exec sp_executesql @stmt =
    N'SELECT TOP(10) a, b
    FROM (VALUES(1,2)) v(a,b)
    OPTION (MAXDOP @n)',
    @params = N'@n int',
    @n = 10

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

I suppose there are many more examples of what works and what doesn't. I'm looking for an authoritative list to eliminate trial and error.

Background

The reason why these sometimes don't work isn't specific to sp_executesql, but rather to how variables work in SQL Server. Your command wouldn't work if you used a local variable and no sp_executesql for MAXOP either.

Per the documentation:


Variables can be used only in expressions, not in place of object names or keywords.

And expressions are defined as:


...a combination of symbols and operators that the SQL Server Database Engine evaluates to obtain a single data value. Simple expressions can be a single constant, variable, column, or scalar function. Operators can be used to join two or more simple expressions into a complex expression.

To someone very familiar with SQL Server the above will make sense and probably not require further explanation, but I can see how this definition may be lacking. Especially since the TOP usage doesn't really look like an expression (even though the value supplied for it is one).

Something easier

It might be a fool's errand to simplify the above, but I'll give it a shot.

A loose guide that is more descriptive and helpful might be:


Parameterized values are valid when they affect the results of the
query.

That is to say that the following would be valid, because they are affecting the results (which rows, how many rows, what value is returned, etc.) but the query is still doing the same thing essentially. That is because they are being used in expressions which modify the query results but do not change the query.

SELECT * FROM Table WHERE Col = @n;
SELECT TOP(@N) * FROM Table;
SET @var = @n;

Whereas these are not valid because they are changing the context of the query, engine settings, or resulting in a different query altogether, etc. Generally changing any of these areas is done through a custom procedure, keyword, function, or by changing objects used in the query and thus do not use an expression.

SELECT * FROM Table OPTION (MAXDOP @n);
USE @n; --Change db context
SELECT * FROM @n;

StackExchange Database Administrators Q#266479, answer score: 6