SQL Server: 存储过程中的可选变量

Question

我想知道是否有任何方法可以将我的存储过程参数设置为可选的。

IF @thing_id <> ''
BEGIN 
 SET @sFiltre = @sFiltre + ' AND OPERES.OPE_THING = ' +  CONVERT(VARCHAR,@thing_id)
END

- Roch

Answer 1

创建存储过程时，请按以下方式创建

Create Proc MyProc
@Param1 VarChar (20),
@Param2 VarChar (20) = NULL
AS

-- Your code here

GO

Param1是必填项

Param2是可选项

- Raj More

---

谢谢你，Raj。那很容易。 - Varun Pozhath

Answer 2

在存储过程参数中提供默认值将使其变为可选项。

编辑:

CREATE PROC [EDURE] [owner.]
procedure_name [; number]
[@parameter data_type
[VARYING] [= default] [OUTPUT]
][,...n]

default

是参数的默认值。 如果定义了默认值，则可以执行该过程而无需为该参数指定值。 默认值必须是常量，或者可以是NULL。 如果过程使用LIKE关键字与参数一起使用，则可以包括通配符字符（％，_，[]和[^]）。

请参阅SQL Server文档：指定参数默认值

- shahkalpesh

Answer 3

是的。在参数列表的末尾列出“可选”参数，并给它们一个默认值（通常为NULL）：

CREATE PROCEDURE MyProcedure
    @param1 int,
    @param2 varchar(200),
    @thing_id int = NULL
AS

    If @thing_id IS NULL Begin
       /* ... */
    End

END

- Joel Coehoorn

---

@Joel：可选参数必须放在最后吗？我认为从可用性的角度来看，最好是这样做。这样对吗？ - shahkalpesh

不是必须的，但这绝对是最佳实践。 - Joel Coehoorn

Answer 4

抛开代码中可能引发的SQL注入问题，是的，你可以这样做。你可以为参数设置默认值。

CREATE PROCEDURE DoStuff @param1 varchar(20) = null

然后在存储过程内部

IF @param1 IS NOT NULL
BEGIN
    ... Do stuff
END

您可以将默认值设置为任何您喜欢的值。

- blowdart

Answer 5

CREATE PROCEDURE SQL_INJECTION(
@MandatoryA int,
@MandatoryB varchar(50),
@MandatoryC datetime,
@OptionalA varchar(50) = NULL
)
AS

-- PUT YOUR DYNAMIC SQL HERE

GO

调用

EXEC dbo.SQL_INJECTION @MandatoryA = 1, @MandatoryB = 'test', @MandatoryC = '2009-10-05', @OptionalA = DEFAULT

注意1：动态SQL = SQL注入

- Rodrigo

---

“动态SQL = SQL注入”并不一定正确。这取决于您是否使用用户提供的值来构建动态SQL字符串。您可以仅基于存储过程内部的逻辑构建动态SQL字符串，然后通过参数将用户提供的值传递到动态语句中。OP的代码容易受到SQL注入攻击，但并非所有动态SQL都会受到攻击。 - nicko