将默认值传递给Table Valued参数 - SQL Server

I am using a table-valued parameter in one our stored procedures. Here is the syntax I used:

我在一个存储过程中使用表值参数。这是我使用的语法：

@districtlist NumericList readonly

(NumericList is the user-defined table type).

（NumericList是用户定义的表类型）。

However, as a requirement I need to pass default values to this table valued parameter.

但是，作为一项要求，我需要将默认值传递给此表值参数。

@districtlist NumericList = 0 readonly

But above code throws a syntax error. Is it possible to pass default value to a table valued parameter? Could someone help me on this?

但是上面的代码会引发语法错误。是否可以将默认值传递给表值参数？有人可以帮我吗？

2 个解决方案

#1

You can opt to not pass the parameter, even if no default is defined for it. For example:

您可以选择不传递参数，即使没有为其定义默认值。例如：

CREATE TYPE TestTable AS TABLE (my_id INT)
GO

CREATE PROCEDURE dbo.Test_TVP
    @my_table TestTable READONLY,
    @my_int INT
AS
BEGIN
    SELECT * FROM @my_table
END
GO

EXEC dbo.Test_TVP @my_int = 2
GO

DROP PROCEDURE dbo.Test_TVP
DROP TYPE TestTable

In this case the table will simply be empty. If you wanted some number of default rows then you would have to simulate that in the stored procedure, probably by using a temporary table since table-valued parameters must be READONLY.

在这种情况下，表格将为空。如果您想要一些默认行，那么您可能必须在存储过程中模拟它，可能是通过使用临时表，因为表值参数必须是READONLY。

#2

You can pass the TVP as default:

您可以默认传递TVP：

EXEC dbo.Test_TVP @my_table = default

Which is the equivalent of an empty table.

这相当于一个空表。

#1