Help! Can't pass names with apostrophe from ASP to SQL7 proc

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,
DaeI don't understand why you are using dynamic sql for this..
I guess you simplified the actual query for the post :)
But anyways.. try this.
getName 'O''''Neil' -- 4 single quotes|||this would work
getname 'o''''niel'
Best Regards
Vadivel
http://vadivel.blogspot.com
"Omnibuzz" wrote:

> I don't understand why you are using dynamic sql for this..
> I guess you simplified the actual query for the post :)
> But anyways.. try this.
> getName 'O''''Neil' -- 4 single quotes|||Or this should work:
SET QUOTED_IDENTIFIER OFF
EXEC getName "O'Anders"
"Vadivel" wrote:
> this would work
> getname 'o''''niel'
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
>
> "Omnibuzz" wrote:
>