Problem:

Microsoft SQL Server Insert statement with quote within value

Resolution:

To specify a value that contains a quote, i.e. Del'Oro, the following can be done:

1. Move the value to a host variable previously:

       01 MYLNAME PIC X(20).

...

           MOVE "Del'Oro" TO MYLNAME

...

           EXEC SQL

               INSERT INTO SQLTEST

               (ID_KEY, LAST_NAME)

               VALUES

               ('123456', :MYLNAME)

           END-EXEC

2. If the whole statement is moved into a host variable to be dynamically executed, the method above can not be used. In this case, 2 single quotes should be used. When SQL Server executes the statement, it will ignore the first quote and interpret the second one as part of the value instead of as a separator.

           MOVE "INSERT INTO SQLTEST " &

                "(ID_KEY, LAST_NAME) " &

                "VALUES ('123456', 'Del''Oro')"

           TO MYSTATEMENT