Storing single quotes in varchar variable SQL Serv

问题:

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).

回答1:

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'''

回答2:

Try to avoid hard-coding values. Create a table to hold these values along with a way to group them.

Table CodeGroups
GroupNumber | Codes
       1    | abc
       1    | def
       1    | ghi
       2    | kkl
       2    | mno
       2    | pqr

This way the user only has to select GroupNumber = 1

You link the CodeGroups table to the table with the code strings. All you have to do to add another code is make an entry in this table and give it a group number.

回答3:

Avoid single qoute problem just by doubling it.

Value Varchar2(10):= ''abc''; ---You will get error.

Value Varchare(10):= '''abc'''; ---Will Solve your problem.

回答4:

                            int varId = "1";
                            String varItem = "Google's root";

                            String strSQL = "INSERT INTO table("
                                            + "id,"
                                            + "item"
                                            +")" 
                                            + "VALUES('" + varId 
                                            + "', '" + varItem
                                            + "')";