在姓氏搜索中添加撇号

我创建了一个过程，它将按姓氏返回申请人列表。我在搜索姓氏中带有撇号的申请人时遇到问题（例如 O'Connor）。您能帮忙找到这些申请人吗：

以下是我的搜索代码：

if Rtrim(@FirstName) <> ''
begin 
  If(Len(@FirstName) < 30) and (CharIndex('%', @FirstName) = 0) and @FirstName != ''
         Set @FirstName = char(39) + @FirstName + '%' + char(39)
end 

if Rtrim(@LastName) <> ''
begin 
   If(Len(@LastName) < 60) and (CharIndex('%', @LastName) = 0) and @LastName != ''
     Set @LastName = Char(39) + @LastName + '%' + char(39)
end 

#At the end  - --Now build dinamically the filter base on input parameters
if Rtrim(@FirstName) <> ''
    select @Where = @Where + ' and a.FirstName like '+ Rtrim(@FirstName) 

if Rtrim(@LastName) <> ''
  select @Where = @Where + ' and a.LastName like '+ Rtrim(@LastName)

您的代码看起来像是在尝试构建动态 SQLWHERE条款。就在那里停止并扔掉它，你的方法是危险且容易出错的。

您可能想做一些类似的事情：

/* declare a few test variables */
DECLARE @FirstName varchar(30)
DECLARE @LastName  varchar(60)
SET @FirstName = 'First''Name'
SET @LastName = 'Last''Name'

/* these variables are for dynamic SQL execution */
DECLARE @IntVariable int
DECLARE @SQLString nvarchar(500)
DECLARE @ParmDefinition nvarchar(500)

/* define a paramertized SQL query */
SET @SQLString =
 N'SELECT 
     UserId 
   FROM 
     UserTable
   WHERE 
     LastName LIKE ''%'' + @ln + ''%'' 
     AND FirstName LIKE ''%'' + @fn + ''%''
  '

/* define the used parameters and their types */    
SET @ParmDefinition = N'@ln varchar(30), @fn varchar(60)'

/* execute dynamic SQL, syntax- and code-injection safely */
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @ln = @LastName, @fn = @FirstName

请务必阅读MSDN on sp_executesql http://msdn.microsoft.com/en-us/library/aa933299%28SQL.80%29.aspx以获得更多解释和示例。