带有默认参数的T-SQL函数。

I have this script:

我有这个脚本:

CREATE FUNCTION dbo.CheckIfSFExists(@param1 INT, @param2 BIT = 1 )
RETURNS BIT
AS
BEGIN
    IF EXISTS ( bla bla bla )
        RETURN 1;
    RETURN 0;
END
GO

I want to use it in a procedure in this way:

我想把它用在这样一个程序中:

IF dbo.CheckIfSFExists( 23 ) = 0
    SET @retValue = 'bla bla bla';

But I get the error:

但我得到了一个错误:

An insufficient number of arguments were supplied for the procedure or function dbo.CheckIfSFExists.

为该过程或函数dbo.CheckIfSFExists提供了数量不足的参数。

Why does it not work?

为什么它不起作用?

4 个解决方案

#1

175

you have to call it like this

你必须这样称呼它

SELECT dbo.CheckIfSFExists(  23, default )

From Technet:

从技术:

When a parameter of the function has a default value, the keyword DEFAULT must be specified when the function is called in order to retrieve the default value. This behavior is different from using parameters with default values in stored procedures in which omitting the parameter also implies the default value. An exception to this behavior is when invoking a scalar function by using the EXECUTE statement. When using EXECUTE, the DEFAULT keyword is not required.

当函数的参数具有默认值时，必须在调用函数时指定关键字default，以便检索默认值。这种行为与在存储过程中使用带有默认值的参数不同，在存储过程中省略参数也意味着默认值。这种行为的一个例外是使用EXECUTE语句调用标量函数。在使用EXECUTE时，不需要默认关键字。

#2

You can call it three ways - with parameters, with DEFAULT and via EXECUTE

您可以用三种方式来调用它——使用参数、默认值和通过EXECUTE

SET NOCOUNT ON;

DECLARE
@Table  SYSNAME = 'YourTable',
@Schema SYSNAME = 'dbo',
@Rows   INT;

SELECT dbo.TableRowCount( @Table, @Schema )

SELECT dbo.TableRowCount( @Table, DEFAULT )

EXECUTE @Rows = dbo.TableRowCount @Table

SELECT @Rows

#3

With user defined functions, you have to declare every parameter, even if they have a default value.

对于用户定义的函数，您必须声明每个参数，即使它们有一个默认值。

The following would execute successfully:

以下将成功执行:

IF dbo.CheckIfSFExists( 23, default ) = 0
    SET @retValue = 'bla bla bla;

#4

One way around this problem is to use stored procedures with an output parameter.

解决这个问题的一种方法是使用带有输出参数的存储过程。

exec sp_mysprocname @returnvalue output, @firstparam = 1, @secondparam=2

exec sp_mysprocname @returnvalue输出，@firstparam = 1， @secondparam=2

values you do not pass in default to the defaults set in the stored procedure itself. And you can get the results from your output variable.

不将默认值传递给存储过程本身中设置的默认值。你可以从输出变量中得到结果。

#1

175