SQL Tutorial: Working with Apostrophes & Dynamic SQL

Welcome to the first in a series of SQL tutorials. These will build over the coming weeks and months to provide useful tips and tricks direct from our SQL server support team.

We had a call last week from a customer with a simple query they provided - pulling some data for a report. The remit of the call was fairly straight-forward: to put this report into an email and send certain details from it to relevant parties, also contained within the report.

I decided to select the result set from the customer query into a temporary table and use a cursor to run through this, compiling the emails which could be sent with sp_send_dbmail. The finished piece would be kept as a stored procedure for our SQL Agent to run as and when.

The snippet of dynamic SQL for the send mail I put together was:

--execution of sp_send_mail:

set @sqlcmd =

@profile_name=''default'''exec msdb..sp_send_dbmail

,@recipients='''+ @email+'''

,@subject=''' +@subj+ ';''

,@body='''+@msg +';''

,@body_format=''html'''

Which was fine in principal, or so I thought ...

After some small hurdles putting something together to construct the email body and subject on the fly, annoyingly both of these parameters had string detail from the base tables with apostrophes scattered here and there. These were all names and titles, e.g. ‘David O'Reilly’ or ‘Santa's Coming To Town’ and so on.

This of course blew the @sqlcmd and its execution. I got around this by cleaning the temp table and then using the replace function to update any columns I felt had the potential to contain apostrophes:

--deal with apostrophes in table...

update #temptable set [columnname] = replace([columnname] ,'''', '''''')

update #temptable set [columnname2] = replace([columnname2] ,'''', '''''')

...Which would leave our 'apostrophe' field content as ‘David O'''Reily’ & ‘Santa'''s Coming To Town’ in the temp table, perfect for our dynamic sql.

Watch out for the next SQL Server tips and tricks blog