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

I would like to know if there is anyway I can set one of my stored procedure parameter as optional.

我想知道无论如何我可以将我的一个存储过程参数设置为可选。

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

5 个解决方案

#1

Providing a default value to the stored procedure parameter will make it optional.

为存储过程参数提供默认值将使其成为可选。

EDIT:

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

CREATE PROC [EDURE] [所有者。 ] procedure_name [; number] [{@parameter data_type} [VARYING] [= default] [OUTPUT]] [,... n]

default

Is a default value for the parameter. If a default is defined, the procedure can be executed without specifying a value for that parameter. The default must be a constant or it can be NULL. It can include wildcard characters (%, _, [], and [^]) if the procedure uses the parameter with the LIKE keyword.

是参数的默认值。如果定义了默认值,则可以在不指定该参数值的情况下执行该过程。默认值必须是常量,或者可以为NULL。如果过程使用带LIKE关键字的参数,则它可以包含通配符(%,_,[]和[^])。

Please see - http://msdn.microsoft.com/en-us/library/aa258259%28SQL.80%29.aspx

请参阅 - http://msdn.microsoft.com/en-us/library/aa258259%28SQL.80%29.aspx

#2

When you create the stored procedure, create it like this

创建存储过程时,请像这样创建它

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

-- Your code here

GO

Param1 is mandatory

Param1是强制性的

Param2 is Optional

Param2是可选的

#3

Yes. List "optional" parameters at the end of the parameter list and give them a default value (typically NULL):

是。列出参数列表末尾的“可选”参数,并为它们提供默认值(通常为NULL):

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

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

END

#4

Setting aside the SQL injection joy that code will bring, yes you can. You can set a default value for parameters

抛开代码将带来的SQL注入快乐,是的,你可以。您可以为参数设置默认值

CREATE PROCEDURE DoStuff @param1 varchar(20) = null

Then inside the stored procedure

然后在存储过程内部

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

You can set the default value to be anything you like.

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

#5

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

-- PUT YOUR DYNAMIC SQL HERE

GO

To call

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

Note1: Dynamic SQL = SQL Injection

注1:动态SQL = SQL注入

#1