SQL Server escape single quote when coming from select statement

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)
GenesisTechAsked:
Who is Participating?
 
ste5anSenior DeveloperCommented:
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.
0
 
xav056Commented:
INSERT INTO TABLE A
           (address1, address2)
     SELECT  address1, replace(address2, "'", "''")  
FROM         TABLE B
WHERE     (id = @ID)
0
 
GenesisTechAuthor 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!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.