Using single quote in an Exec statement in SQL

Question

I am having issues executing a SQL statement from a stored proc having single quotes. Here is my query from a stored procedure that I am executing.

EXEC('UPDATE myTABLE 
         SET myCOLUMN = (SELECT Replace('OSINGLEQUOTEJOHN DOE','SINGLEQUOTE','''')')

I am trying to update table "myTABLE" column "myCOLUMN" with a value "O'John Doe"

The actual query is like this, i tried to simplify it a bit in the above example

EXEC('UPDATE myTABLE 
         SET myCOLUMN = (SELECT Replace('+ @IntegrationGuardian2FullName +','SINGLEQUOTE','''')')

The value of @IntegrationGuardian2FullName is "OSINGLEQUOTEJOHN DOE". Hope that makes more sense.

Can any body help me formatting this query?

Answer 1

Use:

EXEC('UPDATE myTABLE 
        SET myCOLUMN = (SELECT REPLACE(''OSINGLEQUOTEJOHN DOE'',
                                       ''SINGLEQUOTE'',
                                       ''''''''))')

What you provided needed two single quotes around the string, and what to replace, and additional single quotes because of the single quote escaping necessary for dynamic SQL.