SQL 判断参数是否有值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5262159/
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
SQL Determine if parameter has a value
提问by atrljoe
How would I go about using an IFstatement to determine if more than one parameter has a value then do some work, if only one parameter has a value then do other work. Is this possible with the SQL if? I am just trying to determine if one parameter has a value or if multiple parameters has value, because if so then I need to do something completely different. I have tried making a SQL statement but it didnt work properly. Could anyone point me in the right direction?
我将如何使用IF语句来确定是否有多个参数有值，然后做一些工作，如果只有一个参数有值，则做其他工作。如果使用 SQL，这可能吗？我只是想确定一个参数是否具有值或多个参数是否具有值，因为如果是这样，那么我需要做一些完全不同的事情。我试过做一个 SQL 语句，但它没有正常工作。有人能指出我正确的方向吗？
回答by Catch22
In SQL to check if a parameter has a value, you should compare the value to NULL. Here's a slightly over-engineered example:
在 SQL 中检查参数是否有值时，您应该将该值与 NULL 进行比较。这是一个稍微过度设计的示例：
Declare @Param1 int
Declare @param2 int = 3
IF (@Param1 is null AND @param2 is null)
BEGIN
PRINT 'Both params are null'
END
ELSE
BEGIN
IF (@Param1 is null)
PRINT 'param1 is null'
ELSE
IF (@Param2 is null)
PRINT 'param2 is null'
ELSE
PRINT 'Both params are set'
END
回答by DiningPhilanderer
You can check the parameter values for NULL values. But it seems like to me at first blush you should check the parameters BEFORE going to the SQL...
您可以检查参数值是否为 NULL 值。但在我看来，乍一看你应该在进入 SQL 之前检查参数......
Once you know what you need to do then call that SQL or Stored Procedure.
一旦你知道你需要做什么，然后调用该 SQL 或存储过程。
If you do it that way you can simplify your SQL because you only pass the parameters you really need to pass.
如果你这样做，你可以简化你的 SQL，因为你只传递你真正需要传递的参数。
Actually the more I think of this the more important it is to do it this way. You can use the NULL values for other things that way.
实际上，我越是想到这一点，就越以这种方式去做。您可以通过这种方式将 NULL 值用于其他事情。
Easy example: Getting a list of employees. Employees are Active or Inactive - Pass an @IsActive boolean parameter (if true only show active employees, if false show only inactive employees, if NULL then show all the employees)
简单示例：获取员工列表。员工活跃或不活跃 - 传递一个@IsActive 布尔参数（如果 true 只显示活跃员工，如果 false 只显示不活跃员工，如果为 NULL 则显示所有员工）
回答by Bogdan Verbenets
In T-SQL you can use ISNULL function.
在 T-SQL 中，您可以使用 ISNULL 函数。
回答by Thyamine
Here's an example
这是一个例子
if @param1 = 'this value' and @param2 = 'another value'
begin
-- do things here
end
else if @param1 = 'something else'
begin
-- do other work
end
else
-- do different things
回答by mohsensajjadi
The answer provided by @Catch22 is good for your question. But using IFs in particular and procedural routines in general in sqlshould be kept to a minimum. So maybe the following solution using a casestatement might better fit what you want to do:
@Catch22 提供的答案非常适合您的问题。但是IF特别是使用s 和一般的过程例程sql应该保持在最低限度。因此，使用case语句的以下解决方案可能更适合您想要执行的操作：
select Case When @Param1 is null and @Param2 is null Then ...-- both params null
When @Param1 is null then .... -- param1 is null
When @Param2 is null then .... -- param2 is null
Else .... -- both params set
End
