sql搜索查询多个可选参数

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.

我正在尝试在我的文档存档系统上为高级搜索页面编写查询。我正在尝试通过多个可选参数进行搜索。我有大约5个参数,可能是空字符串或搜索字符串。我知道我不应该检查每个字符串或空,并为每个组合创建一个单独的存储过程。

Edit: Ended up using:

编辑:结束使用:

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

6 个解决方案

#1

You could use COALESCE (or ISNULL) like so:

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

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

#2

I usually do this :P

我经常这样做:P

WHERE (@var1 IS NULL OR col1 = @var1)
AND (@var2 IS NULL OR col2 = @var2)

...

#3

You can put OR's in your WHERE clause like so:

您可以将OR放在WHERE子句中,如下所示:

WHERE 
   (@var1 = '' OR col1 = @var1) AND
   (@var2 = '' OR col1 = @var2) AND
   (@var3 = '' OR col1 = @var3) ...

#4

An alternative is to dynamically built the SQL in the Stored Procedure, this produces the best possible plan for the query and a plan will be created and used anyway (in 2005 and above).

另一种方法是在存储过程中动态构建SQL,这将为查询生成最佳可能的计划,并且无论如何都将创建和使用计划(2005年及以上)。

#5

You can pass optional parameters to a stored procedure but the optimizer will build a plan based on the specific calls you make to that proc. There are some tricks in SQL Server 2005 and later to avoid this (parameter sniffing, 'with no compile' hints, etc.)

您可以将可选参数传递给存储过程,但优化器将根据您对该proc进行的特定调用来构建计划。 SQL Server 2005及更高版本中有一些技巧可以避免这种情况(参数嗅探,'没有编译'提示等)

Even with that, tho, I prefer to build a view with the core of the query and then use that view in several procs with the specific parameters. That allows SQL to optimize as it wants/should and I still get to consolidate the query specifics.

即便如此,我更喜欢使用查询的核心构建视图,然后在具有特定参数的多个过程中使用该视图。这允许SQL根据需要进行优化,我仍然可以整合查询细节。

#6

Even better is to make the parameter optional NULL and then test in the WHERE clause just like the empty string case...

更好的是使参数可选为NULL,然后在WHERE子句中测试,就像空字符串情况一样...

#1