Tuesday, 29 November 2016

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.


No comments:

Post a Comment

c++ - Does curly brackets matter for empty constructor?

Those brackets declare an empty, inline constructor. In that case, with them, the constructor does exist, it merely does nothing more than t...