Escaping Single Quotes ( )

Escaping Single Quotes (')

Let's look at a slightly modified version of the SQL from Figure 7.3. Consider the following UPDATE statement:

    SET v_dynSQL = 'UPDATE EMPLOYEE SET BONUS=' ||                    CHAR(p_new_bonus) || 'WHERE LASTNAME = ''BROWN'' '; 

Notice that in this example, the WHERE clause with the search string 'BROWN'' is hard-coded in the statement text. Because LASTNAME is a character column, the search predicate must be a string. It means that the value BROWN must be quoted like 'BROWN' in the dynamic SQL text. This can be accomplished by prefixing the single quote with an additional single quote, as shown in the previous example. This tells DB2 that the single quotes are escaped and they are part of the SQL statement.

If you were to use the SQL procedure as presented in Figure 7.3, you could simply pass LASTNAME = 'BROWN' as p_where_cond.