SQL Server: assigning string literals to a parameter

I need to do something like:

EXEC sp_ExecuteSQL N'select * from Members where Name like @pat',
   N'@pat nvarchar(max)', @pat=Tom

My question is about the last parameter.

When the value to assign is a "simple" string, i.e. no spaces etc, I could use something like @pat=Tom, no quotes needed.

However, when the value to assign ends with the % character for example, it won't work, and I have to do something like @pat=N'Tom%'.

Is this how the syntax of assigning a string literal is defined?

Does it also mean that if my value contains the single quotation mark, I would have to escape it manually?

Answer

I am surprised that it works with no quotes. This must be a peculiarity of exec.

Always include the single quotes:

EXEC sp_ExecuteSQL N'select * from Members where Name like @pat',
   N'@pat nvarchar(max)', @pat='Tom';

According to the documentation:

If the value of a parameter is an object name, character string, or
qualified by a database name or schema name, the whole name must be
enclosed in single quotation marks. If the value of a parameter is a
keyword, the keyword must be enclosed in double quotation marks.

I don't know why it would work without the single quotes.