I am having trouble adding a field to a string. Right now I have a stored procedure running on a giant string (everything inside '') but I am running into problems when creating the Where statement.
I have:
' Cast(Name AS varchar(max)) NOT IN (''Jimmy' + CHAR(39) + 's'')'
But I am getting an error with the CHAR(39). How can I effectively make it look like Jimmy's inside the string? I know it's probably a stupid fix, but please help.
You need to double it up again since the apostrophe has to survive two rounds of delimiters. Try:
' Cast(Name AS varchar(max)) NOT IN (''Jimmy''''s'')'
For example, compare:
DECLARE @sql NVARCHAR(255) = N'SELECT ''Jimmy' + CHAR(39) + ''';';
EXEC sp_executesql @sql;
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string 'Jimmy';'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'Jimmy';'.
And this:
DECLARE @sql NVARCHAR(255) = N'SELECT ''Jimmy''s'';';
EXEC sp_executesql @sql;
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ';'.
Finally:
DECLARE @sql NVARCHAR(255) = N'SELECT ''Jimmy''''s'';';
EXEC sp_executesql @sql;
Result:
-------
Jimmy's
