• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 230
  • Last Modified:

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 ?

0
vd
Asked:
vd
  • 3
  • 3
1 Solution
 
kponderCommented:
Change all occurances of ' in the name to '' (that is 2 's)




0
 
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.)

0
 
kponderCommented:
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?

Thanks
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
kponderCommented:
Oops

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

0
 
vdAuthor Commented:
Do you have some sql that would change all embedded 's to "s in a particular column?  
0
 
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.

0
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.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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