SQL Server存储过程中的可选参数

Question

我正在SQL Server 2008中编写一些存储过程。这里是否支持可选输入参数的概念？

我想我可以始终将不想使用的参数传入NULL，检查存储过程中的值，然后从那里开始处理，但我想知道这里是否支持该概念。

- larryq

---

仔细阅读Erland的网站，他在动态搜索条件方面有一些很棒的信息：http://www.sommarskog.se/dyn-search.html - Aaron Bertrand

Answer 1

你可以这样声明：

CREATE PROCEDURE MyProcName
    @Parameter1 INT = 1,
    @Parameter2 VARCHAR (100) = 'StringValue',
    @Parameter3 VARCHAR (100) = NULL
AS

/* Check for the NULL / default value (indicating nothing was passed) */
if (@Parameter3 IS NULL)
BEGIN
    /* Whatever code you desire for a missing parameter */
    INSERT INTO ........
END

/* And use it in the query as so */
SELECT *
FROM Table
WHERE Column = @Parameter

- Raj More

---

如果参数的类型是唯一标识符(uniqueidentifier)，该怎么办？例如：@userId uniqueidentifier。 - R K Sharma

回答@RKSharma，对于任何想知道的人-使用唯一标识符也是一样的。 - Maximilian Riegler

Answer 2

是的，没错。将参数声明为:

@Sort varchar(50) = NULL

现在你甚至不需要传递参数。它将默认为NULL（或您选择的任何默认值）。

- Mike Cole

---

你甚至不需要 = NULL。 - OMG Ponies

你确定你不需要它吗？ - Mike Cole

天啊，如果你不包括“= <NULL|some default value>” ，那么这个参数就是必需的。你可以将其作为NULL传递，但那样你只是将那个逻辑转移到使用该过程的应用程序中。 - Aaron Bertrand

补充亚伦的观点。如果你在现有的存储过程中添加一个新的可选参数，最好使用“= NULL”。原因是你可能不知道所有调用该存储过程的代码。因此，除非你使用“= NULL”将其设置为可选项，在可能遗漏输入值的所有位置都会导致错误。 - nanonerd

nanonerd：至少在2014年及以上版本中，您可以设置一个默认值，这样当您不传递该参数时，它将使用该默认值而不会出错。至少对我来说，在2014年的版本中是这样工作的。 - billpennock

Answer 3

在 SQL Server 2014 及以上版本中，您可以设置默认值，当您不传递该参数时，它将采用该默认值并不会出错。 部分示例：第三个参数被添加为可选项。仅使用前两个参数执行（exec）实际过程是正常的。

exec getlist 47,1,0

create procedure getlist
   @convId int,
   @SortOrder int,
   @contestantsOnly bit = 0
as

- billpennock

---

太好了！上次我尝试这个是在 Sql 2012 版本，那时候仍然需要传入 NULL 才能获取默认值。很高兴知道他们最终取消了这个要求，并使它们真正成为可选项。 - eidylon

Answer 4

前面的答案中提到的默认值只适用于简单情况。在更复杂的情况下，我会在存储过程的开头附近使用IF子句来提供一个值，如果参数为NULL或空，并且需要进行计算。

我经常在WHERE子句中使用可选参数，并发现SQL不会短路逻辑，因此使用CASE语句确保不尝试评估NULL或空日期或唯一标识符，如下所示：

CREATE Procedure ActivityReport
(
    @FromDate varchar(50) = NULL,
    @ToDate varchar(50) = NULL
)

AS

SET ARITHABORT ON

IF @ToDate IS NULL OR @ToDate = '' BEGIN
    SET @ToDate = CONVERT(varchar, GETDATE(), 101)
END

SELECT ActivityDate, Details
FROM Activity
WHERE
1 = CASE
   WHEN @FromDate IS NULL THEN 1
   WHEN @FromDate = '' THEN 1
   WHEN ActivityDate >= @FromDate AND ActivityDate < DATEADD(DD,1,@ToDate) THEN 1
   ELSE 0
END

- Bruce Patin