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

Solving the UK's productivity puzzle - working harder or smarter?

How can your Manufacturing business benefit from constant production management by adopting data modernisation, digital transformation and the cloud?

Read more
View All