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

I'm writing some stored procs in SQL Server 2008, and wondered if the concept of optional input parameters is possible here?

我正在SQL Server 2008中编写一些存储过程，并想知道这里是否可以使用可选输入参数的概念？

I suppose I could always pass in NULL for parameters I don't want to use, check the value in the stored proc, then take things from there, but I was interested if the concept is available here. Thanks!

我想我总是可以为我不想使用的参数传递NULL，检查存储过程中的值，然后从那里拿东西，但我感兴趣的是这里有这个概念。谢谢！

2 个解决方案

#1

152

You can declare like this

你可以像这样声明

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

#2

Yes, it is. Declare parameter as so:

是的。声明参数如下：

@Sort varchar(50) = NULL

Now you don't even have to pass the parameter in. It will default to NULL (or whatever you choose to default to).

现在你甚至不必传入参数。它将默认为NULL（或任何你选择默认的）。

#1

152