Stored procedures with ' in parameter

I have a stored procedure that includes a parameter for a
person's last name. There are some last names with an apostophe  in them, like O'Leary, which cause errors in the stored procedure.
Specifics: @last_name varchar (30)
Line in the SQL statement: And Last_name like '"+@last_name+"%'
This works fine for all names except those like O'Leary.
Any ideas ?

Who is Participating?
kponderConnect With a Mentor Commented:
Change all occurances of ' in the name to '' (that is 2 's)

vdAuthor Commented:
Thanks. I don't think that would work because users wouldn't expect to have to
search for  O"leary instead of  O'leary....

(Sorry about the delay in response, there was a server problem at Exp. Exch.)

1. If you are using a custom front end (VB app?), you can
   have the code change the apostrophes for you.

2. Or try this:  And Last_name like @last_name+ '%'

3. If either of these work,  could you post the entire
   SQL statement?

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.


3. If Neither of these work,  could you post the entire
   SQL statement?

vdAuthor Commented:
Do you have some sql that would change all embedded 's to "s in a particular column?  
vdAuthor Commented:
Thanks. Yes, this approach works. I created a stored procedure to do the conversion;
the front-end guy is making appropriate changes there.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.