Possibly one of the most difficult parts of dynamic SQL is dealing with single quotation marks.  I'm guessing most DBAs at one point or another have seen the following:

REPLACE(@quotedvar, '''', '''''')

For those who haven't seen this, or don't really understand it, what the code is doing is replacing all of the single quotes (') with 2 single quotes ('').  I'll go into the why a little farther down.

Here are my are 2 rules when dealing with single quotes. 

1. The outside 2 single quotes delimit the string.
2. On the inside of the string you must have 2 single quotes for each single quote you are representing.

First let's break down the strings the REPLACE is using: '''' and ''''''. The first thing I'm going to do is to color the outside two quotes so that we see what we are working with a bit more clearly.

'''' and ''''''

Now we can see the inside quotes a bit more clearly.  Note again there are 2 single quotes for each single quote we want to represent.  If it helps, think of putting O'Neil into a string.  You would write @var = 'O''Neil'.  Then if you get rid of the letters you end up with @var = ''''.  Hopefully this also makes '''''' a little easier to understand.  When you look at it try to ignore the outside quotes and see the inside quotes in pairs.  So '''''' actually represents ''.

Why would we want to mess with this?  Since T-SQL uses 's to delimit strings there has to be a way to put a single quote inside of the string.  (I'm not going into QUOTED_IDENTIFIER here.  If you are curious look it up in BOL.)  For example the compiler is going to have a hard time understanding 'O'Neil'.  Is it the string O'Neil?  The string Neil with a mistaken 'O at the beginning?  Or the string O with a mistaken Neil' at the end.  It's very similar to the problem of extra commas in a comma delimited file.  The way this is handled is by using two single quotes.  We put 'O''Neil' and the compiler is happy, it understands that what you are trying to say is O'Neil. 

Sounds simple right?  So let's try it out.

DECLARE @quotedvar nvarchar(100)
DECLARE @sql nvarchar(1000)
SET @quotedvar = 'O''Neil'
SET @sql = 'PRINT ''' + @quotedvar + ''''
PRINT @sql
EXEC sp_executesql @sql

The output looks like this:

PRINT 'O'Neil'

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'Neil'.

Msg 105, Level 15, State 1, Line 1

Unclosed quotation mark after the character string ''.

Now I hear someone muttering at the back of the class saying “I put in the two single quotes like you told me but it's still wrong!”

And they would be right.  We put 2 single quotes in each SET statement.  But note, when we printed the @sql statement we got “PRINT 'O'Neil'”.  Single quotes both before and after O'Neil just like we intended.  However, when we run it, we are back to 'O'Neil' again.  We stored 'O''Neil' into @quotedvar, why didn't it transfer correctly?  Let’s look.

SET @quotedvar = 'O''Neil'

Well first the quotes on the outside delimit the string so they are ignored when the value is stored into the variable.  That would be why the extra single quotes in the SET @sql statement.  ( SET @sql = 'PRINT''' + @quotedvar + '''' )  But remember, when the value was stored into the variable the two single quotes ('') were translated into a single quote (').  So now the variable has “O'Neil” in it.  What we need to be stored in @sql is “PRINT 'O''Neil'”. 

Now everyone go back to the top, I'll wait.

Everyone back?  Ok, here we go

DECLARE @quotedvar nvarchar(100)
DECLARE @sql nvarchar(1000)
SET @quotedvar = 'O''Neil'
SET @sql = 'PRINT ''' + REPLACE(@quotedvar,'''','''''') + ''''
PRINT @sql
EXEC sp_executesql @sql

Look familiar?  Now our output looks like this:

PRINT 'O''Neil'

O'Neil

Everyone follow?  Good.  Now for homework please fill in the following:

DECLARE @topsql nvarchar(200)
SET @topsql =
      'DECLARE @quotedvar nvarchar(100) ' + char(13) +
      'DECLARE @sql nvarchar(1000) ' + char(13) +
…..
…..
…..
…..
…..
      'PRINT @sql ' + char(13) +
      'EXEC sp_executesql @sql '
PRINT @topsql
PRINT '-------'
EXEC sp_executesql @topsql

If you look closely this piece of code takes the previous example prints it out and then and runs it dynamically.  In case you have never tried it before this would be similar to dynamically creating dynamic SQL.

As a clue the output should look like this:

DECLARE @quotedvar nvarchar(100)

DECLARE @sql nvarchar(1000)

SET @quotedvar = 'O''Neil'

SET @sql = 'PRINT ''' + REPLACE(@quotedvar,'''','''''') + ''''

PRINT @sql

EXEC sp_executesql @sql

-------

PRINT 'O''Neil'

O'Neil

I’ll put the answer in the comments next week!