将撇号添加到姓氏搜索 [英] Add apostrophe to last name search

问题描述

我创建了一个proc，它将通过姓氏返回申请人的列表。搜索具有撇号的姓氏的申请人（例如O'Connor）时遇到问题。你能帮助找到那些申请人：

I created a proc that will return a list of applicants by lastname. I have a problem searching Applicants with last name that has apostrophe (Example O'Connor). Could you please help finding those applicants:

以下是我的搜索代码：

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)

推荐答案

您的代码看起来像您尝试构建动态SQL WHERE 子句。阻止它在那里，把它丢弃，你的方法是危险的，容易出错的。

Your code looks like you try to build a dynamic SQL WHERE clause. Stop it right there and throw it away, your approach is dangerous and error-prone.

你可能想要这样做：

/* 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 ，以获得更多的解释和说明样本。

Be sure to read the MSDN on sp_executesql for more explanation & samples.

这篇关于将撇号添加到姓氏搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！