存储过程LIKE查询返回非预期结果的问题排查与修复

我在存储过程中编写了如下查询：

CREATE PROCEDURE [s_Staff_ByLikeLastNmByLikeFirstNm]
@LastNm varchar(10), @FirstNm varchar(10)
/*WITH ENCRYPTION*/
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT Staff.FirstNm, Staff.LastNm
FROM Staff
WHERE Staff.LastNm LIKE @LastNm + '%' AND Staff.FirstNm LIKE @FirstNm + '%'

当我调用该存储过程时传入@LastNm参数值为'Christiansen'，它返回了'Christiansen'和'Christianson'两条结果，表现得更像是执行了SOUNDEX搜索。但如果我在存储过程外单独执行该SELECT查询，却能得到正确的结果。请问该如何修复这个问题？

这问题其实是参数长度截断导致的，很容易被忽略！

你看，存储过程里定义的@LastNm是varchar(10)，但你传入的'Christiansen'长度是12个字符——超过了参数的最大长度，SQL Server会自动把超出部分截断，只保留前10个字符：'Christians'。

这时候你的WHERE条件就变成了：

Staff.LastNm LIKE 'Christians%'

而'Christiansen'和'Christianson'的前10个字符都是'Christians'，所以两者都会被匹配到，看起来就像SOUNDEX的效果，但本质是截断后的模糊匹配范围变大了。

单独执行SELECT时你直接用了完整的字符串'Christiansen'，没有截断，所以只会匹配正确的那条记录。

修复步骤很简单：

• 把存储过程里的参数长度改得足够大，比如改成varchar(50)（或者根据你实际的Staff.LastNm字段长度来设置，最好和表字段的长度保持一致）：

  CREATE PROCEDURE [s_Staff_ByLikeLastNmByLikeFirstNm]
  @LastNm varchar(50), @FirstNm varchar(50) -- 调整为合适的长度
  /*WITH ENCRYPTION*/
  AS
  SET NOCOUNT ON
  SET TRANSACTION ISOLATION LEVEL READ COMMITTED
  SELECT Staff.FirstNm, Staff.LastNm
  FROM Staff
  WHERE Staff.LastNm LIKE @LastNm + '%' AND Staff.FirstNm LIKE @FirstNm + '%'
  

• 同时检查@FirstNm的参数长度，避免同样的问题发生。

另外，你可以用这个语句验证截断的情况：

DECLARE @TestNm varchar(10) = 'Christiansen'
SELECT @TestNm, LEN(@TestNm) -- 会输出'Christians'和10

内容的提问来源于stack exchange，提问作者thecoolmacdude