Solved

Stored procedures with ' in parameter

Posted on 1998-12-02
6
218 Views
Last Modified: 2010-03-19
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
Comment
Question by:vd
  • 3
  • 3
6 Comments
 
LVL 1

Accepted Solution

by:
kponder earned 100 total points
ID: 1092035
Change all occurances of ' in the name to '' (that is 2 's)




0
 

Author Comment

by:vd
ID: 1092036
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
 
LVL 1

Expert Comment

by:kponder
ID: 1092037
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 1

Expert Comment

by:kponder
ID: 1092038
Oops

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

0
 

Author Comment

by:vd
ID: 1092039
Do you have some sql that would change all embedded 's to "s in a particular column?  
0
 

Author Comment

by:vd
ID: 1092040
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now