How to handle apostrophes in Dynamic SQL?

@ova 

In dynamic SQL, you should handle apostrophes by using the appropriate escape character. In most cases, this means using two consecutive apostrophes ('') to represent a single apostrophe in a string.

For example, consider the following dynamic SQL statement that creates and populates a table:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

DECLARE @sql AS NVARCHAR(MAX);

SET @sql = N'CREATE TABLE #temp (
                id INT,
                name NVARCHAR(50)
             );

INSERT INTO #temp (id, name)
VALUES (1, N''John''s phone'');';

EXEC sp_executesql @sql;

In this example, the name 'John's phone' includes an apostrophe, so it is necessary to use two consecutive apostrophes to represent the single apostrophe in the string. This ensures that the dynamic SQL statement is interpreted correctly.

It's also a good idea to use parameterized queries whenever possible, as this can help to prevent issues with special characters and SQL injection attacks.

@ova 

Here is an example of using parameterized queries with apostrophes in dynamic SQL:

1 2 3 4 5 6 7

DECLARE @sql AS NVARCHAR(MAX); DECLARE @name AS NVARCHAR(50) = N'John''s phone';

SET @sql = N'CREATE TABLE #temp ( id INT, name NVARCHAR(50) );

INSERT INTO #temp (id, name) VALUES (1, @name);';

EXEC sp_executesql @sql, N'@name NVARCHAR(50)', @name;

In this example, the @name parameter is used in the dynamic SQL statement, and it is passed as a separate parameter to the sp_executesql procedure. This ensures that the apostrophe in the @name parameter is handled correctly, without the need for using escape characters.