Eric R. Eric R. - 1 month ago 12
SQL Question

Storing single quotes in varchar variable SQL Server 2008

I was wondering if there is a way to store single quote marks in SQL Server 2008. I am building several reports up and all these reports are the exact same, except they only differ in the codes I am selecting upon. For example, one report uses codes 'abc', 'def', 'ghi' and another report uses codes 'jkl', 'mno', 'pqr'. I was thinking to reduce the number of Stored Procedures I will have to make, I could make a parameter on the report to choose which type of report to run. Based off of that, I would use the correct codes. So I was going to store these codes in a varchar variable. Below is the functionality I was hoping for:

DECLARE @codes1 varchar, @codes2 varchar
SET @codes1 = ''abc', 'def', 'ghi''
SET @codes2 = ''jkl', 'mno', 'pqr''


Then, I was going to use the proper varchar variable based on the parameter the user chooses. The only problem is setting the variables since the string will have single quotes in it (the string will be used in an SQL 'IN' statement, thats the reason for the single quotes being present).

Answer

Like this. Yes Oded is correct. The proper terminology for this is 'escaping'. You can escape a single quote ' by doubling it up ''

DECLARE @codes1 varchar(50), @codes2 varchar(50)
SET @codes1 = '''abc'', ''def'', ''ghi'''
SET @codes2 = '''jkl'', ''mno'', ''pqr'''