How do I escape a single quote in dynamic SQL

Question

I have the following query on SQL Server, some of the values are stored in variables computed earlier:

SET @Command = N'INSERT INTO Products (Id
                                ,Region
                                ,Name
                                ,Category
                                ,CreatedBy
                                ,CreatedOn
                                ,) SELECT ' + @Id + ',
                                            Region, 
                                            ''' + @ProductName + ''', 
                                            Category,
                                            CreatedBy,
                                            CreatedOn FROM ' + @ProductTable + ' 
                                                WITH (NOLOCK) WHERE Id IS NOT NULL';

    EXEC(@Command)

It runs fine except if the value of @ProductName contains quotes(e.g. Jim's Product) in which case I get the following error:

Unclosed quotation mark after the character string

Is there a way to handle single quotes in a variable in a dynamic query like this, where one of the selected values being inserted (@ProductName in this case) is directly the value to be inserted instead of an actual column name on the source table whose value needs to be retrieved for insertion?

Answer 1

The best way is to use sp_executesql instead of EXEC and use proper parameter for the @ProductName value.

The rest of the query that can't be parameterized (the name of the table @ProductTable) will remain dynamic string concatenation.

In this case you don't need to escape anything and you are protected against SQL injection.

Something like this:

SET @Command = 
    N'INSERT INTO Products
        (Id
        ,Region
        ,Name
        ,Category
        ,CreatedBy
        ,CreatedOn)
    SELECT
        @ParamId
        ,Region
        ,@ParamProductName
        ,Category
        ,CreatedBy
        ,CreatedOn
    FROM ' + @ProductTable + N' WITH (NOLOCK)
    WHERE ID IS NOT NULL'
    ;

EXEC sp_executesql
    @Command
    ,N'@ParamId int, @ParamProductName nvarchar(255)'
    ,@ParamId = @Id
    ,@ParamProductName = @ProductName
;