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 drives up its share of the Database MSP Market with
record contract growth and 2nd acquisition

DSP firmly established itself as one of the UK’s fastest growing proactive Database MSPs during 2016, signing £2.1m of new contracts and making its second acquisition, the Oracle DBA support division of IT Services provider ITSB.

Read more
View All

Event

Applications on Oracle Database Appliance (ODA)

A main goal... how to future proof your applications environment through Oracle while becoming more efficient in terms of costs and productivity.

As well as the option of developing the fundamental steps you need to take to get ...

Read more
View All

Blog

How can you regularly benchmark your database and application infrastructure against real-life? Why would you want to?

In the age of big data, public cloud, private cloud it’s easy to be pushed into constantly thinking about the future... What should you optimise though and how can you be sure it’ll make a difference? Would knowing it could give you a competitive edge be worth considering?

Read more
View All