We help IT Professionals succeed at work.

SQL Server escape single quote when coming from select statement

443 Views
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:

INSERT INTO TABLE A
           (address1, address2)
     SELECT  address1, address2  
FROM         TABLE B
WHERE     (id = @ID)
Comment
Watch Question

Commented:
INSERT INTO TABLE A
           (address1, address2)
     SELECT  address1, replace(address2, "'", "''")  
FROM         TABLE B
WHERE     (id = @ID)
Senior Developer
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
ste5an:

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!!!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.