News & Events

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

Get in touch

For more information or to schedule a demo please contact us

Contact us

News

DSP winners at the UK Oracle User Group Awards 2017/18

DSP attended the UK Oracle User Group Partner of the Year Awards last week, and we came away with awards for both categories in which we were nominated

Read more
View All

Event

"Self Managing Databases: Fable, Fantasy or the Future"

Join us for lunch and to hear some war stories from staff and customers with experience of Oracle Database Cloud Service. We'll also understand more about the Autonomous Database and how Oracle will compete with AWS.

Read more
View All

Blog

Why is modernizing your Security key for a move to the Cloud?

Don't compromise when it comes to the security of your data: discover the key to capitalizing on your migration into Public Cloud.

Read more
View All