sql server 动态查询空格问题

一个奇怪的bug，之前遇到过，今天再次遇到了，这里说一下，先看如下SQL：

-- 存在这个表则删除
IF OBJECT_ID(N't_pl_table',N'U') is not NULL
BEGIN
DROP TABLE t_pl_table
END
-- 创建一个表用来测试
CREATE TABLE t_pl_table
(
    item_no char(20)
)
-- 随便插入一条数据
INSERT INTO t_pl_table(item_no) VALUES('123456')
-- 常规like查询
SELECT * FROM t_pl_table WHERE item_no LIKE '123456'
-- 使用EXEC动态查询
EXEC ('SELECT * FROM t_pl_table WHERE item_no LIKE ''123456''')
DECLARE @SearchSQL NVARCHAR(MAX)
SET @SearchSQL = 'SELECT * FROM t_pl_table WHERE item_no LIKE @item_no'
-- 参数化动态查询1
EXEC sp_executesql @SearchSQL,N'@item_no nvarchar(20)',@item_no='123456'
-- 参数化动态查询2
EXEC sp_executesql @SearchSQL,N'@item_no varchar(20)',@item_no='123456'

　　参数化动态查询1里面返回的结果是空的，现在对比SQL可以很明显看到是动态参数的类型不对， 应该是varchar，而不是nvarchar，两者的区别自行百度。

　　只是一般我觉得这两个类型差别不大，没想到在动态SQL查询这里有明细的结果区别。

　　看表结果，item_no是char类型，这个类型和varchar以及nvarchar都不一样，char(20)，当字符串只有6位时，其余位会补空格，所以查出来的123456实际是“123456              ”，而不是“123456”

　　这也是为什么动态参数查询时指定参数类型为nvarchar无法得到正确结果的原因。

　　既然是空格，首先表结构是不允许改的，char就是char，设计如此，随便改表可能会引发一系列问题，那么怎么做呢，可能你会想到下面这个方法：

SET @SearchSQL = 'SELECT * FROM t_pl_table WHERE rtrim(item_no) LIKE @item_no'

　　用rtrim去掉空格再like不就好啦，好啦？

　　从结果上看确实可以解决问题，但是从做法上看不可取，where后面的列加函数，会影响索引，查询的时候不走索引会影响性能，所以这么做不是最优解。

　　最后的做法就是动态参数化查询时参数的类型要和列的类型匹配。

　　PS：C#里面，参数化查询是通过new SqlParameter实现的，最终生成的SQL之所以用nvarchar的原因在于new SqlParameter的时候没有指定参数类型，不指定默认就是nvarchar，一般不会有问题，但遇到上述情况就不行了。

 

-- 错误的示例
new SqlParameter("@item_no", itemNo) 
-- 正确的示例
new SqlParameter("@item_no", itemNo) { SqlDbType = SqlDbType.VarChar, Size = 100 }

　　以上。