Pass Default value to Table Valued parameter - SQL Server

Question

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).

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?

Since this is a **table**, really, you can either define a *table instance* with the default values added to it, or you can use `@districtlist NumericList = NULL` as your parameter (passing `NULL` as "default" value). — marc_s, Mar 22 '16 at 12:35
@districtlist NumericList = NULL doesn't work for me . it throws "Operand type clash: NULL is incompatible with NumericList" error — bmsqldev, Mar 22 '16 at 12:37

score 33 · Accepted Answer · answered Aug 25 '17 at 18:43

33

You can pass the TVP as default:

EXEC dbo.Test_TVP @my_table = default

Which is the equivalent of an empty table.

answered Aug 25 '17 at 18:43

Remi Lemarchand

863
6
4

1

This is only way to pass it to a UDF. – Oleksandr Dec 19 '20 at 00:23
Using `default` SQL Server 15.0.2101.7 gives the same error message as if you used `NULL`: `Operand type clash: NULL is incompatible...` – pasx Jul 13 '23 at 15:21

score 21 · Answer 2 · answered Mar 22 '16 at 12:39

21

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.

answered Mar 22 '16 at 12:39

Tom H

46,766
14
87
128

2

If you try to pass it as `NULL` then it will fail. For example, `@my_table = NULL` will give you an error that `NULL` is incompatible with your table data type. – Tom H Mar 22 '16 at 13:11
what i meant is , in c# code i just use null or 0 in the insert list and pass the list into sql code. – bmsqldev Mar 22 '16 at 15:37

Pass Default value to Table Valued parameter - SQL Server

2 Answers2

Linked