SQL search on strings: "O'SHEA" , "O'RYAN"

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL search on strings: "O'SHEA" , "O'RYAN"

Author

Topic

jszulc
Yak Posting Veteran

66 Posts

Posted - 2008-02-08 : 15:29:39

My stored procedure returns an error when I try to search on Irish last names, do you know how I can modify it to find Irish last names?
Thank you.

ALTER PROCEDURE [dbo].[sp_SearckKPITBLNEW]
@SelectedCategory nvarchar(50),
@SearchWord nvarchar(200)

AS
DECLARE @SQL varchar(1000)

SET @SQL = 'select *,T3.Address as [Finish Location Address],T3.City as [Finish Location City], T3.State as [Finish Location State]
from fnKPIStartLocations() T1
LEFT OUTER JOIN dbo.RealEstate T3 ON T1.PropertyIDFinishLoc = T3.PropertyID WHERE TaskId > 0'
IF (((@SelectedCategory IS NOT NULL) OR (@SelectedCategory != '')) AND @SelectedCategory = 'Lead')
SET @SQL = @SQL + ' AND Lead like ''%' + @SearchWord + '%'''
IF (((@SelectedCategory IS NOT NULL) OR (@SelectedCategory != '')) AND @SelectedCategory = 'Responsible')
SET @SQL = @SQL + ' AND Responsible like ''%' + @SearchWord + '%'''
IF (((@SelectedCategory IS NOT NULL) OR (@SelectedCategory != '')) AND @SelectedCategory = 'PM')
SET @SQL = @SQL + ' AND PM like ''%' + @SearchWord + '%'''

Print '@SQL = ' + @SQL
EXEC(@SQL)

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-08 : 15:37:28

You need to be VERY CAREFUL whenever you use Dynamic SQL because it opens you up to SQL Injection attacks. Here is a version that should work without Dynamic SQL and will also solve your problem.


Select *,
T3.Address as [Finish Location Address],
T3.City as [Finish Location City], 
T3.State as [Finish Location State]
From fnKPIStartLocations() T1
Left Outer Join dbo.RealEstate T3 ON T1.PropertyIDFinishLoc = T3.PropertyID 
Where TaskId > 0 
And IsNull(@SelectedCategory, '') In ('Lead', 'Responsible', 'PM')
And Case
	When IsNull(@SelectedCategory, '') = 'Lead' Then Lead
	When IsNull(@SelectedCategory, '') = 'Responsible' Then Responsible
	When IsNull(@SelectedCategory, '') = 'PM' Then PM
End Like '%' + @SearchWord + '%'

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-08 : 15:41:24

When using a single quote in a string such as O'RYAN you must use 2 single quotes (O''RYAN) to escape it being used to indicate the beginning or end of a string. In your situation this gotcha is compounded by the use of a dynamic script. Because you are passing the string twice, once to @SearchWord and then a second time to @SQL, you must duplicate your escaping so that there are 4 single quotes for each single quote in your search word: SET @SearchWord = 'O''''RYAN'

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-08 : 15:52:07

Again, please don't use Dynamic SQL for this. You are opening yourself up to a very dangerous attack.

jszulc
Yak Posting Veteran

66 Posts

Posted - 2008-02-08 : 16:04:48

Qualis Thank you. It Works !

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-09 : 01:23:53

More on dynamic searsches
www.sommarskog.se/dyn-search.html

Madhivanan

Failing to plan is Planning to fail