Query Parameter on TVP - parameters cannot by DbNull

Question

Hey

Until recently this worked ok (or at least i thought it did). I am passing a table row in a parameter to a stored procedure but i am getting an error

Table-valued parameters cannot be DbNull

Im assuming its a prevention on SQL server side as in my stored procedure there isnt a default value set because i cant find a way of doing that.

My table parameter if of a type that i have created, here is my top code

ALTER PROC [dbo].[usp_tbl_KLA_ItemUpdateRowByTable] 
  @tblItemListType ItemListType READONLY,
  @job_id int = NULL,
  @item_id int = NULL,
  @rev_no int = NULL

and this is the error i get

Because of this i am unable to proceed with the query. Whats is a reasonable solution to get around passing a default value of a TableType to the stored procedure?

Thanks

Nacho

Answer 1

Whats is a reasonable solution to get around passing a default value of a TableType to the stored procedure?

How can we say, without knowing what your procedure do with the variable?

Answer 2

Hi @NachitoMax

As the User Define table Types are created as table-valued, so you cannot assign null to a table.

Most parameters to a SP are required unless a default value is set with = in the parameters list. In the case of a TVP, you cannot set a default value but you don't have to. SQL doesn't complain if you execute the SP without specifying a value for that parameter.

See this similar thread for more details: Can table-valued parameter be null?

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

The simple answer is that you leave it out. All TVPs have an implicit default value of the empty table.

Answer 4

Hi

I have referred this back to DevExpress. The editor they provide cannot validate the parameter but if i code the query & parameters in the designer, it qualifies and works. It seems the query editor cannot allow an empty parameter value.

Thanks for everyones help :)