I have a proc that does wildcard searches on names which works fine
expect with names that contain an apostrophe like O'Neil.

When O'Neil is sent to the proc, I get "Incorrect syntax near 'Neil'."
This tells me I've got a delimiter issue.

I've tried replacing the single apostrophe with a double or even using
quotes as the delimiter and still get an error.  What should I try; can
anyone help?

BTW, here's an example of the proc.
=====================================================

CREATE proc getName
@CUSTNAME varchar(15)
as
declare @SQL varchar(4000)

set @SQL = 'select PARENT_NAME, CITY, STATE, ZIP from
[CS-170].CUST.dbo.CUST_ADR
where
PARENT_NAME like ''%' + @CUSTNAME + '%'''
exec (@SQL)

======================================================

I've passed the name as below and still get errors.

getName O'Neil
getName 'O'Neil'
getName ''O'Neil''      - All Single quotes chr (39)
getName "O'Neil"       - Quotes on the outside chr(34)
getName 'O''Neil'       - All Single quotes chr (39)
getName "O''Neil"      - Quotes on the outside chr(34), and double
singles in the name chr(39)

Nothing works due to the syntex.  What can I do, please HELP!!

Thank you,

Dae

Can you first try this as i think this should work if ColumnName is a varchar.

select * from TableName where ColumnName = 'O''Hanlon'

Then I would try either of the following.

1.) The replace function in .NET/ASP and'or
2.) The replcae function in TSQL.

Look at Books On Line for this as there are some simple examples there.

marcmc wrote:
Everyone, thanks for your suggestions.  I'm going to go with the four
single quotes, but it worked perfect!!!  YEAH!!!

THANK YOU!!!!!!!

- Dae

A better approach is to use the parameters object and not concatenate
strings.

In the example you gave, your stored procedure does not need to concatenate
either.  The following line will do the same thing.

select PARENT_NAME, CITY, STATE, ZIP from
[CS-170].CUST.dbo.CUST_ADR
where
PARENT_NAME like '%' + @CUSTNAME + '%'

To understand why you should not be using concatenation, check out the
following links on SQL injection.

http://www.sqlservercentral.com/columnists/mcoles/updatedsqlinjection.asp

http://www.nextgenss.com/papers/advanced_sql_injection.pdf

<crab.***@gmail.com> wrote in message
Show quoteHide quote

Help with looping through records in stored procedure
Turn this SP into a view!
Tough SQL problem, need expert advice!!!
Advice Requested : Trying to write portable SQL
Date Parsing using T-SQL
find the first row of ordered records that sum is less than a cert
Insert by Parameter
trigger will not execute
Using LIKE operator and spacing to search SPs
2 questions on Sql server query