SQL Server escape single quote when coming from select statement

Posted on 2014-08-05
Last Modified: 2014-08-05
I have a Stored Procedure that inserts a new record into table A by pulling the data from Table B based upon an ID.

The address field from Table B, which needs to be inserted into Table A, may contain a Single Quotation tick mark.

Currently it causes an error.  How do I deal with this?

My Code looks like this:

           (address1, address2)
     SELECT  address1, address2  
FROM         TABLE B
WHERE     (id = @ID)
Question by:GenesisTech
    LVL 9

    Expert Comment

               (address1, address2)
         SELECT  address1, replace(address2, "'", "''")  
    FROM         TABLE B
    WHERE     (id = @ID)
    LVL 32

    Accepted Solution

    This SQL statement is correct and should not raise an error.

    What error message do you get?

    The only thing I can image: there is a trigger active, creating some dynamic SQL, e.g. a  handcrafted change data capture.

    Author Closing Comment


    Thank you for your response. It makes sense that I should not have to do anything special and that SQL Server would handle it correctly without me having to do a replace.

    My ASP Page threw the error and, since it was the 1st time ever that the address had a single quote mark, I assumed it was the stored procedure that caused it.

    When I executed it directly in SQL Server there was no error and it worked fine. That led me to look at the ASP page closer and find where the problem is.

    Thank you!!!

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Suggested Solutions

    If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
    So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now