• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 226
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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