Cranialsurge Cranialsurge -4 years ago 108
SQL Question

How do I escape a single quote in dynamic SQL

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 Source

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
;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download