Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1795
  • Last Modified:

Apostrophe in WHERE clause in a dynamic SQL statement

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 + ''''

EXEC (@vchSQL)



0
Yongshu Li
Asked:
Yongshu Li
  • 2
1 Solution
 
SjoerdVerweijCommented:
SELECT @vchSQL ='UPDATE ' + @vchDataSource +
                              '  SET Sales_Total= ' + CONVERT(VARCHAR, @mnySales_Total_ByCustomer) +
                              ',  Last_Update = '''  + CONVERT(VARCHAR, @dtmCurrentDate)  +
                    '''  WHERE Customer_Name = ''' +   replace(@vchCustomerName,  '''', '''''') + ''''

Note that the second parameter to Replace is 4 single quotes; the third 6 single quotes.
0
 
SjoerdVerweijCommented:
Actually,

SELECT @vchSQL ='UPDATE ' + @vchDataSource +
                              '  SET Sales_Total= ' + CONVERT(VARCHAR, @mnySales_Total_ByCustomer) +
                              ',  Last_Update = '''  + CONVERT(VARCHAR, @dtmCurrentDate)  +
                    '  WHERE Customer_Name = ''' +   replace(@vchCustomerName,  '''', '''''') + ''''
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now