Escaping single quote in SQL Server

by

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.

More Related Contents:

  1. Max Nbr of Months W/ No Activity in Past 12 Months
  2. Export from SQL Server to Excel
  3. SQL server query to output the data shown below
  4. hide/remove header and footer
  5. Will ANSI JOIN vs. non-ANSI JOIN queries perform differently?
  6. What is the difference between Integrated Security = True and Integrated Security = SSPI?
  7. How to use a variable for the database name in T-SQL?
  8. What is the difference between char, nchar, varchar, and nvarchar in SQL Server?
  9. How do you view ALL text from an ntext or nvarchar(max) in SSMS?
  10. SQL Server: SELECT only the rows with MAX(DATE)
  11. Missing artifact com.microsoft.sqlserver:sqljdbc4:jar:4.0
  12. Create a view with ORDER BY clause
  13. What does ‘COLLATE SQL_Latin1_General_CP1_CI_AS’ do?
  14. INSERT INTO vs SELECT INTO
  15. Possible to store value of one select column and use it for the next one?
  16. LEN function not including trailing spaces in SQL Server
  17. How to view transaction logs in SQL Server 2008 [closed]
  18. Scheduled run of stored procedure on SQL server
  19. How to write UTF-8 characters using bulk insert in SQL Server?
  20. Best way to implement an audit trail in SQL Server?
  21. How can I change from SQL Server Windows mode to mixed mode (SQL Server 2008)?
  22. How can I Insert data into SQL Server using VBNet
  23. Using NOLOCK Hint in EF4?
  24. Will GETUTCDATE() return the same value if used twice in the same statement?
  25. Convert decimal time to hours and minutes
  26. Remove all spaces from a string in SQL Server
  27. Create table (structure) from existing table
  28. SQL Query Where Column = ” returning Emoji characters 🎃 and 🍰
  29. To run a SSIS package outside of SQL Server Data Tools you must install Move File to Archive of Integration Services or higher
  30. How to calculate difference in hours (decimal) between two dates in SQL Server?

Leave a Comment