sql搜索查询多个可选参数 [英] sql search query for multiple optional parameters

问题描述

我正在尝试在我的文档归档系统上写一个针对高级搜索页面的查询.我正在尝试通过多个可选参数进行搜索.我有大约5个参数，它们可以是空字符串或搜索字符串.我知道我不必检查每个字符串或为空并为每个组合创建一个单独的存储过程.

I'm trying to write a query for an advanced search page on my document archiving system. I'm attempting to search by multiple optional parameters. I have about 5 parameters that could be empty strings or search strings. I know I shouldn't have to check for each as a string or empty and create a separate stored procedure for each combination.

最终使用:

ISNULL(COALESCE(@var, a.col), '') = ISNULL(a.col, '')

推荐答案

您可以像这样使用COALESCE(或ISNULL):

You could use COALESCE (or ISNULL) like so:

WHERE COALESCE(@var1, col1) = col1 
AND COALESCE(@var2, col2) = col2 
AND COALESCE(@var3, col3) = col3

这篇关于sql搜索查询多个可选参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！