Apostrophe in WHERE clause in a dynamic SQL statement
Posted on 2004-09-07
Could anyone help me with the following apostrophe problem with MS SQL Server 2000? I searched this site but couldn't find a good solution. Some answers are about the INSERT or UPDATE part of the SQL statement, NOT the WHERE part as what I need.
NOTE: The following is used in a SP on the server, NOT via a Front End.
I have a dynamic SQL statement to update data in tables, but it will fail whenever the field 'Customer_Name' contains an apostrophe in the WHERE part.
Here are some details:
-- some code omitted, including variables....
-- Update 'Sales_Total' in Table '@vchDataSource (this table name is dynamic, such as 'tblSales_Summary').
-- On this particular occasion, Customer_Name (@vchCustomerName ) is unique but may contain apostrophe(s), which will casue a problem.
SELECT @vchSQL ='UPDATE ' + @vchDataSource +
' SET Sales_Total= ' + CONVERT(VARCHAR, @mnySales_Total_ByCustomer) +
', Last_Update = ''' + CONVERT(VARCHAR, @dtmCurrentDate) +
''' WHERE Customer_Name = ''' + @vchCustomerName + ''''