如何在存储过程中的 t-sql 动态语句中使用 LIKE? [英] How to use LIKE in a t-sql dynamic statement in a stored procedure?
问题描述
我正在尝试使用带有 % 通配符包装参数的 LIKE 关键字,但我不确定如何将 % 字符放入语句中而不破坏它.现在我有:
I'm trying to use the LIKE keyword with the % wildcards wrapping the parameter, but I'm not sure how to get the % characters into the statement without breaking it. Right now I have:
SET @SQLQuery = 'SELECT * FROM [tblApps] WHERE [firstName] LIKE %@search%'
我在我的 .net 应用程序中收到一个 SqlException 错误,显示运行它时‘@search’附近的语法不正确.如果我删除 @search 参数周围的 % 字符,错误就会消失.
I get a SqlException error in my .net app that says "Incorrect syntax near '@search' when I run it. The error goes away if I remove the % characters surrounding the @search parameter.
推荐答案
% 字符必须在搜索字符串...
The % characters have to be in the search string...
SET @search = '%' + @search + '%'
SET @SQLQuery = 'SELECT * FROM [tblApps] WHERE [firstName] LIKE @search'
请注意,以下内容也可以使用,但会引入潜在的 SQL 注入漏洞...
Note that the following would also work, but introduces potential for a SQL injection vulnerability...
-- DON'T do this!
SET @SQLQuery = 'SELECT * FROM [tblApps] WHERE [firstName] LIKE ''%' + @search + '%'''
这篇关于如何在存储过程中的 t-sql 动态语句中使用 LIKE?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!