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?

@Augwa: The variables are fetched from another block in the same query earlier up. This particular snippet is fed those values and is currently dynamic. Did you have an alternative in mind? — Cranialsurge, Mar 01 '17 at 05:35

Vladimir Baranov · Accepted Answer · 2017-03-01T05:53:40.957

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
;

Perfect, exactly what I needed! Thanks @Vladimir. – Cranialsurge Mar 01 '17 at 09:36 — Cranialsurge, Mar 01 '17 at 09:36

How do I escape a single quote in dynamic SQL

1 Answers1