SQL Server 2008，一个查询的不同 WHERE 子句

问题描述

我有一个存储过程，它采用相同的列，但具有不同的 WHERE 子句.

I have a stored procedure which takes the same columns but with different WHERE clause.

类似的东西.

SELECT
     alarms.startt, alarms.endt, clients.code, clients.Plant,
     alarms.controller, alarmtype.atype, alarmstatus.[text]
FROM alarms
INNER JOIN clients ON alarms.clientid = clients.C_id
INNER JOIN alarmstatus ON alarms.statusid = alarmstatus.AS_id
INNER JOIN alarmtype ON alarms.typeid = alarmtype.AT_id

并且我在 3 个 if(条件)中放置了相同的查询，其中 WHERE 子句根据传入变量的参数发生变化.

and I put the same query in 3 if's (conditions) where the WHERE clause changes according the parameter passed in a variable.

我是否必须为每个 if 中的每个条件一遍又一遍地编写整个字符串?

Do I have to write the whole string over and over for each condition in every if?

或者我可以将它优化一次，唯一会改变的是 WHERE 子句?

Or can I optimize it to one time and the only thing what will change will be the WHERE clause?

推荐答案

如果您执行以下操作，则可以避免重复代码:

You can avoid repeating the code if you do something like:

WHERE (col1 = @var1 AND @var1 IS NOT NULL)
OR ...
OPTION RECOMPILE;

您还可以通过数据库的参数化设置(简单与强制)对此行为产生一些影响.

You can also have some effect on this behavior with the parameterization setting of the database (simple vs. forced).

避免重复代码并避免因参数嗅探而导致次优计划的方法是使用动态 SQL:

Something that avoids repeating the code and avoids sub-optimal plans due to parameter sniffing is to use dynamic SQL:

DECLARE @sql NVARCHAR(MAX) = N'SELECT ...';
IF @var1 IS NOT NULL
  SET @sql = @sql + ' WHERE ...';

如果您启用了服务器设置优化临时查询"，这可能会更好.

This may work better if you have the server setting "optimize for ad hoc queries" enabled.

这篇关于SQL Server 2008，一个查询的不同 WHERE 子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！