如何在存储过程中的 t-sql 动态语句中使用 LIKE？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5383634/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use LIKE in a t-sql dynamic statement in a stored procedure?
提问by Industrial Themes
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:
我正在尝试使用带有 % 通配符包装参数的 LIKE 关键字，但我不确定如何将 % 字符放入语句中而不破坏它。现在我有：
SET @SQLQuery = 'SELECT * FROM [tblApps] WHERE [firstName] LIKE %@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.
我在我的 .net 应用程序中收到一个 SqlException 错误，显示“运行它时'@search'附近的语法不正确。如果我删除@search 参数周围的 % 字符，错误就会消失。
回答by Av Pinzur
The % characters have to be inthe search string...
% 字符必须在搜索字符串中...
SET @search = '%' + @search + '%'
SET @SQLQuery = 'SELECT * FROM [tblApps] WHERE [firstName] LIKE @search'
Note that the following would also work, but introduces potential for a SQL injection vulnerability...
请注意，以下内容也可以使用，但会引入潜在的 SQL 注入漏洞...
-- DON'T do this!
SET @SQLQuery = 'SELECT * FROM [tblApps] WHERE [firstName] LIKE ''%' + @search + '%'''
回答by hova
SET @SQLQuery = 'SELECT * from [tblApps] WHERE [firstName] LIKE ''%'' + @search + ''%'''
exec sp_executesql @query=@SQLQuery, @params=N'@search nvarchar(96)', @search=@search
The only difference from this version as compared to the others is that the dynamic execution of the sql is in fact parameterized which mitigates sql injection a bit.
与其他版本相比，此版本的唯一区别是 sql 的动态执行实际上是参数化的，这稍微减轻了 sql 注入。
回答by Neil Knight
SET @search = '%' + @search
SET @SQLQuery = 'SELECT * FROM [tblApps] WHERE [firstName] LIKE ' + @search + '%'
回答by VPP
This worked for me!
这对我有用！
SET @search = '''%' + @search + '%'''
SET @SQLQuery = 'SELECT * FROM [tblApps] WHERE [firstName] LIKE' + @search
EXEC sp_executesql @SQLQuery
回答by sohan yadav
declare @Cmd nvarchar(2000)
declare @eName varchar(10)
set @eName='a'
set @Cmd= 'select * from customer1 where name LIKE '''+'%' +@eName+ '%' + ''''
print @Cmd
EXECUTE sp_executesql @Cmd
