如何处理可变数量的参数 [英] How to deal with variable number of arguments

问题描述

我创建了这样的存储过程:

Hi,
I created a stored procedure like this:

CREATE PROCEDURE storedTest
@param1 DateTime,
@param2 int = null,
@param3 nvarchar(50) = null
AS

我已经读过，如果用默认值声明参数，则可以将可变数量的参数传递给存储过程.例如，我想执行以下命令:

I''ve read that if the parameters are declared with default value, you can pass variable number of arguments to a stored procedure. For example, I want to execute command like this:

EXEC storedTest @param1='8/9/2011 00:00:00',@param2=1

或

OR

EXEC storedTest @param1='8/9/2011 00:00:00',@param3='hi'

然后，我尝试检查将哪些参数传递给存储过程，所以我这样写:

Then I tried to check which parameters were passed to the stored procedure, so I wrote this:

IF(@param2 = null AND @param3 = null)
--do something
IF(@param2 != null AND @param3 = null)
--do something
--I examined all the conditions
ELSE
--do something else

但是当我执行此操作时，我总是得到ELSE语句的结果.

如何检查传递的参数?


在此先谢谢您.

But when I executed this, I always got the result of the ELSE statement.

How can I examine the passed arguments?


Thanks in advance.

推荐答案

如果不传递参数，将采用默认值.
因此，如果使用以下参数调用SP，则param3将为null.

If you don''t pass the parameter, default value will be taken.
So if you call the SP with following parametes, param3 will be null.

EXEC storedTest @param1='8/9/2011 00:00:00',@param2=1

在这种情况下，如果条件不成立，请首先

in this case first if condition will not be true

IF(@param2 = null AND @param3 = null)

因为第二个如果else有所不同，那么这也不是正确的，它将出现在else语句中.

since second if else is different, this is also not true and it will come to else statement.

IF(@param2 != null AND @param3 = null)
--do something
--I examined all the conditions
ELSE

EXECstoredTest @ param1 ="8/9/2011 00:00:00''，@ param3 =" hi''
在这种情况下，@ param2为空，但是@ param3不为空，因此这两个条件都将不起作用...

如果执行以下命令，则"IF"条件可能为真:
EXECstoredTest @ param1 =''2011年8月9日00:00:00''

同样，将null与null进行比较不会给您正确的结果.始终检查IS NULL而不是@ param1 = null
希望这对您有帮助...

EXEC storedTest @param1=''8/9/2011 00:00:00'',@param3=''hi''
in this case, @param2 is null, but @param3 is not null so both conditions will not work...

"IF" condition might be true in case of following execution:
EXEC storedTest @param1=''8/9/2011 00:00:00''

also comparing null with null will not give you proper results. Always check for IS NULL instead of @param1 = null
hope this helps...

这篇关于如何处理可变数量的参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！