Nice article and a classic subject.
One workaround is to use tokens when writing dynamic SQL statements, especially if nested more than one level;
DECLARE @TOKEN NVARCHAR(1) = NCHAR(123)
DECLARE @SQ NVARCHAR(1) = NCHAR(39)
DECLARE @SQLSTR NVARCHAR(MAX) = N'
DECLARE @TOKEN NVARCHAR(1) = NCHAR(124)
DECLARE @SQ NVARCHAR(1) = NCHAR(39)
DECLARE @SQLSTR NVARCHAR(MAX) = N{
DECLARE @TOKEN NVARCHAR(1) = NCHAR(125)
DECLARE @SQ NVARCHAR(1) = NCHAR(39)
DECLARE @SQLSTR NVARCHAR(MAX) = N|
DECLARE @TOKEN NVARCHAR(1) = NCHAR(126)
DECLARE @SQ NVARCHAR(1) = NCHAR(39)
DECLARE @SQLSTR NVARCHAR(MAX) = N}
DECLARE @quotedvar nvarchar(100) = N~O¡Neil~
SELECT @quotedvar = REPLACE(@quotedvar,NCHAR(161),NCHAR(39))
PRINT @quotedvar
}
PRINT @@NESTLEVEL
SELECT @SQLSTR = REPLACE(@SQLSTR,@TOKEN,@SQ)
PRINT @SQLSTR
EXEC sp_executesql @SQLSTR
|
PRINT @@NESTLEVEL
SELECT @SQLSTR = REPLACE(@SQLSTR,@TOKEN,@SQ)
PRINT @SQLSTR
EXEC sp_executesql @SQLSTR
{
PRINT @@NESTLEVEL
SELECT @SQLSTR = REPLACE(@SQLSTR,@TOKEN,@SQ)
PRINT @SQLSTR
EXEC sp_executesql @SQLSTR
'
PRINT @@NESTLEVEL
SELECT @SQLSTR = REPLACE(@SQLSTR,@TOKEN,@SQ)
PRINT @SQLSTR
EXEC sp_executesql @SQLSTR
Execution result;
0
DECLARE @TOKEN NVARCHAR(1) = NCHAR(124)
DECLARE @SQ NVARCHAR(1) = NCHAR(39)
DECLARE @SQLSTR NVARCHAR(MAX) = N'
DECLARE @TOKEN NVARCHAR(1) = NCHAR(125)
DECLARE @SQ NVARCHAR(1) = NCHAR(39)
DECLARE @SQLSTR NVARCHAR(MAX) = N|
DECLARE @TOKEN NVARCHAR(1) = NCHAR(126)
DECLARE @SQ NVARCHAR(1) = NCHAR(39)
DECLARE @SQLSTR NVARCHAR(MAX) = N}
DECLARE @quotedvar nvarchar(100) = N~O¡Neil~
SELECT @quotedvar = REPLACE(@quotedvar,NCHAR(161),NCHAR(39))
PRINT @quotedvar
}
PRINT @@NESTLEVEL
SELECT @SQLSTR = REPLACE(@SQLSTR,@TOKEN,@SQ)
PRINT @SQLSTR
EXEC sp_executesql @SQLSTR
|
PRINT @@NESTLEVEL
SELECT @SQLSTR = REPLACE(@SQLSTR,@TOKEN,@SQ)
PRINT @SQLSTR
EXEC sp_executesql @SQLSTR
'
PRINT @@NESTLEVEL
SELECT @SQLSTR = REPLACE(@SQLSTR,@TOKEN,@SQ)
PRINT @SQLSTR
EXEC sp_executesql @SQLSTR
2
DECLARE @TOKEN NVARCHAR(1) = NCHAR(125)
DECLARE @SQ NVARCHAR(1) = NCHAR(39)
DECLARE @SQLSTR NVARCHAR(MAX) = N'
DECLARE @TOKEN NVARCHAR(1) = NCHAR(126)
DECLARE @SQ NVARCHAR(1) = NCHAR(39)
DECLARE @SQLSTR NVARCHAR(MAX) = N}
DECLARE @quotedvar nvarchar(100) = N~O¡Neil~
SELECT @quotedvar = REPLACE(@quotedvar,NCHAR(161),NCHAR(39))
PRINT @quotedvar
}
PRINT @@NESTLEVEL
SELECT @SQLSTR = REPLACE(@SQLSTR,@TOKEN,@SQ)
PRINT @SQLSTR
EXEC sp_executesql @SQLSTR
'
PRINT @@NESTLEVEL
SELECT @SQLSTR = REPLACE(@SQLSTR,@TOKEN,@SQ)
PRINT @SQLSTR
EXEC sp_executesql @SQLSTR
4
DECLARE @TOKEN NVARCHAR(1) = NCHAR(126)
DECLARE @SQ NVARCHAR(1) = NCHAR(39)
DECLARE @SQLSTR NVARCHAR(MAX) = N'
DECLARE @quotedvar nvarchar(100) = N~O¡Neil~
SELECT @quotedvar = REPLACE(@quotedvar,NCHAR(161),NCHAR(39))
PRINT @quotedvar
'
PRINT @@NESTLEVEL
SELECT @SQLSTR = REPLACE(@SQLSTR,@TOKEN,@SQ)
PRINT @SQLSTR
EXEC sp_executesql @SQLSTR
6
DECLARE @quotedvar nvarchar(100) = N'O¡Neil'
SELECT @quotedvar = REPLACE(@quotedvar,NCHAR(161),NCHAR(39))
PRINT @quotedvar
O'Neil
Eirikur