Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Server escape single quote when coming from select statement

Posted on 2014-08-05
3
Medium Priority
?
432 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)
0
Comment
Question by:GenesisTech
3 Comments
 
LVL 9

Expert Comment

by:xav056
ID: 40241523
INSERT INTO TABLE A
           (address1, address2)
     SELECT  address1, replace(address2, "'", "''")  
FROM         TABLE B
WHERE     (id = @ID)
0
 
LVL 36

Accepted Solution

by:
ste5an earned 2000 total points
ID: 40241601
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
 

Author Closing Comment

by:GenesisTech
ID: 40242152
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Loops Section Overview

810 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