Escaping single quote in SQL Server

Question

I was trying to execute the below statement to escape single quotes (i.e. using two single quotes):

declare @year varchar(max)
set @year = '111,11';
exec ('SELECT * FROM SplitValues(' + @year + ','','')');

I even tried to use char(39) instead of quotes:

declare @year varchar(max)
set @year = '111,11';
exec ('SELECT * FROM SplitValues(' + @year + ',' + char(39) + ',' + char(39) + ')');

But it didn't help. These are the only two solutions that I found on this site. Any help?

This is the simplified query to clear up all your questions:

declare @year varchar(max)
set @year = '111,11';
SELECT * FROM SplitValues(@year , ',')

I want to achieve this, but using a dynamic query.

Answer 1

A word of advice. When testing a dynamic script, first just display it instead of executing it. That way you will be able to see it exactly as it would be seen by the EXEC statement.

Now to the issue. You should keep in mind that you are not passing the variable to SplitValues but are instead concatenating the variable's value into the script. Since the value is varchar, it should be concatenated with quotation marks around it. The absence of them is the only problem really.

The quotes around the second argument, the comma, are escaped correctly in both cases. So, just use either of the methods to add the quotes around the first argument:

• repetition of the quotation mark:

  DECLARE @year varchar(max), @sql varchar(max);
  SET @year = '111,11';
  SET @sql = 'SELECT * FROM SplitValues(''' + @year + ''','','')';
  SELECT @sql;
  

• using CHAR(39):

  DECLARE @year varchar(max), @sql varchar(max);
  SET @year = '111,11';
  SET @sql = 'SELECT * FROM SplitValues(' + CHAR(39) + @year + CHAR(39) + ',' + CHAR(39) + ',' + CHAR(39) + ')';
  SELECT @sql;
  

Obviously, the first method is more compact, but, like I said, both work well, as this SQL Fiddle demo clearly shows.

Note, however, that you could easily escape this issue in the first place, if you pardon the pun. Instead of EXEC (), you could use EXEC sp_executesql, which allows you to use parameters. Here's the same script rewritten to use sp_executesql:

DECLARE @year varchar(max), @delim char(1);
SET @year = '111,11';
SET @delim = ',';
EXEC sp_executesql
  N'SELECT * FROM SplitValues(@year_param,@delim_param)',
  N'@year_param varchar(max), @delim_param char(1)',
  @year,@delim;

As you can see, no need to worry about escaping the quotes: SQL Server takes the trouble of substituting the values correctly, not you.