Handling single quotes in dynamic SQL in Stored Procedures
Posted on 2013-01-16
How do I format usage of single quotes when building a dynamic SQL statement in a stored procedure? The dynamic SQL statement itself will contain single quotes.
Here’s the SQL that I want the SP to run:
SET [UploadStatus] = 'NOUPLOAD'
WHERE ID = ''
This runs fine when I run it as shown, as a normal static update command.
Here's the dynamic SQL statement I'm working on (I am passing the @wsName parameter from a step in the Job that calls the SP):
SET @SQL1 = 'UPDATE [' + @wsName + '].DATABASE.dbo.TRANSACTIONS
SET [UPLOADSTATUS] = ' + 'NOUPLOAD' +
'WHERE ID = ' + ''''
However, I'm running into problems because the dynamic SQL needs to contain single quotes.
How do I differentiate a single quote as a delimiter of the dynamic SQL statement I'm building, and a single quote as one that I actually want in the resulting dynamic SQL?