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.
No comments:
Post a Comment