How to pass default value for a sp parameter via TableAdapter Fill method

Question

Technology is VB.Net and Sql Server. I have a stored procedure

CREATE OR ALTER PROC [dbo].[sp_bring_some_data] 
    (        
      @My_Data as myTableValuedType READONLY,
      @My_Context as bit
    )
    AS
    BEGIN
      IF @My_Context = 0 
        BEGIN
          -- this sp accepts a table-valued params
          EXEC sp_bring_some_data_0 @My_Inner_Data = @My_Data
        END
      ELSE
        BEGIN
          -- this sp is parameterless
          EXEC sp_bring_some_data_b
        END
    END

This sp is a SelectCommand for a TableAdapter. Of course the autogenerated fill method has three params:

Public Overloads Overridable Function Fill(ByVal dataTable As ds.sp_fill_some_dataDataTable, ByVal My_Data As Object, ByVal My_Context As Global.System.Nullable(Of Boolean)) As Integer

In some context of my application I do not have a proper DataTable object for @My_Data param. I would like to be able to run my sp with @My_Context = 1 in which I do not need the data table for my calcuiations.

It appears I cannot pass Nothing into my Fill function, as table-valuded params do not accept nulls. As I have read here, I can pass default as a param on sql side to pass a proper empty table.

My problem is how to force TableAdapter's Fill method to pass default do the sql server.

My workaround is to prepare a proper dummy DataTable to pass when there is no real one, but I hope there is some nicer approach.

Thanks in advance!

Thom A · Answer 1 · 2019-05-15T10:16:16.633

0

Like I said in my (now deleted) comment, you still need to provide the parameter @My_Data, but it doesn't have to have any rows in it. Syntax like the below is perfectly acceptable:

DECLARE @My_Data dbo.myTableValuedType;

EXEC [dbo].[sp_bring_some_data], @My_Data, 1;

I don't INSERT any rows into @My_Data but the parameter is still passed, it's just empty. In your case, you would still declare the parameter in VB.Net, but then that's it; you wouldn't fill it with a dataset.

In Table-Value parameter terms, an empty table is the effectively the equivalent of not passing the parameter (when it has a default value) for a Scalar Parameter.

Edit:

My VB.Net isn't great, but I would guess you would create an empty dataset for pass to a table-value parameter as something like this:

Dim sqlConn As New SqlConnection(connString)
Dim sqlComm As New SqlCommand("dbo.sp_bring_some_data", sqlConn)

sqlComm.CommandType = CommandType.StoredProcedure

Dim dt As New DataTable
Dim dr As DataRow

dt.Columns.Add("IDColumn",Type.GetType("System.Int32"))
dt.Columns.Add("Column1",Type.GetType("System.String"))

sqlComm.Parameters.AddWithValue("@My_Data",dt)

sqlComm.Parameters.Add(New SqlParameter("@My_Context ", SqlDbType.Bit))
sqlComm.Parameters("@My_Context ").Value = chkMyCheckBox.Value 'Don't know if that works in VB.net in truth, I assume it does

So, I generate the data table, but never fill it. Someone (far) better at VB.Net will know if this is wrong.

I realise this should be in a Using or something, but like I'm not a VB.Net programmer, so I'm working on basics from Googling, documentation, and some (old) knowledge from my VB6 days.

edited May 15 '19 at 10:16

answered May 15 '19 at 09:58

Thom A

88,727
11
45
75

My question is slighty different. I want to handle that situation in my VB.Net code in an elegant way. At the moment I am creating a dummy DataTable object with proper cloumn count and pass it to the Fill method. I wonder if there is some trick on .net side which will automagically pass empty table or 'default' keyword to the sql server – Kuba D May 15 '19 at 10:02
@KubaD, the issue is that an auto-generated will add all the proc parameters. For the default TVP use case, create a command without the TVP parameter and the TVP will be empty in the proc. – Dan Guzman May 15 '19 at 10:05
My VB.net is rusty, but maybe something like that, @KubaD – Thom A May 15 '19 at 10:11
@Lamu, thanks for your update, really appreciated (I'd never write any single VB line without real need :) )! Anyway the problem is still slighty different. In my project I am using VisualStudio autogenerated datasets. I have an autogenerated Fill method and I would like to achieve the result from that level. – Kuba D May 15 '19 at 10:30
Afraid, I'm not idea then @KubaD, sorry. Like I said, my VB.Net isn't great, as I don't use it, and when I did study, I was using VB6. ;) – Thom A May 15 '19 at 10:38
@Dan Guzman - seems that my workaround has some extra benefits so I'll keep it. Next time I'll gladly follow your suggestion. – Kuba D May 15 '19 at 11:57

How to pass default value for a sp parameter via TableAdapter Fill method

1 Answers1