在 TSQL 的 CASE 中使用 LIKE
我想选择包含 @selctDescription 参数内容的记录，但仅当 @selctDescription 不为空时。
我有以下内容，但不起作用：
(t.[description] LIKE
(
CASE
WHEN @selctDescription = '' THEN t.[description]
ELSE ('%' @selctDescription '%')
END
)
)
任何人都可以指出我正确的方向吗？
I would like to select the records that contain the content of the @selctDescription parameter but only when @selctDescription is not empty.
I have the following, which does not work:
(t.[description] LIKE
(
CASE
WHEN @selctDescription = '' THEN t.[description]
ELSE ('%' @selctDescription '%')
END
)
)
Can anyone point me in the right direction?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
我认为你的代码相当于：
另一方面，你的描述表明你想要这个：
I think your code is equivalent to:
Your description on the other hand, suggests you want this:
给你一些想法...
如果你有
'%' @selctDescription '%'，你只需要在字符串之间使用+来连接它们。然后您的代码将按原样运行。'%' + @selctDescription + '%'另外，值得注意的是，您甚至不需要
CASE语句，因为当参数为空时，您会得到'%%'，它仍然会匹配所有内容。了解这一点很有用，因为目前
LIKE语句的两侧都有表字段。这确实会伤害优化器。如果我要使用 CASE，我会更倾向于坚持...这样做的好处是，在执行查询之前，可以将 CASE 语句的结果作为标量值执行一次。而不是每行都重新计算。
也就是说，它在功能上变得与......相同
A couple of thoughts for you...
Where you have
'%' @selctDescription '%', you just need+between the strings to concatenate them. Your code will then work as is.'%' + @selctDescription + '%'Also, it's useful to note that you don't even need the
CASEstatement, as when the parameter is blank, you get'%%', which will still match everything.This is useful to know because at present you have table fields on both sides of the
LIKEstatement. This will really hurt the optimiser. If I were to use CASE I'd be more tempted to stick to...This has the benefit that the result of the CASE statement can be performed once, as a scalar value, prior to execution of the query. As opposed to being recalculated ever row.
And that said, it then becomes functionally identical to...
根据您的第一行和注释，您需要进行以下选择：
但是您必须输入正确的表和字段，并且@selctDeciption必须是文本（char、nchar、 varchar、nvarchar、...）。
Based on your first line and comments, you need to make the following select:
But you must put the correct table and field and @selctDesciption must be text (char, nchar, varchar, nvarchar, ...).