title: Pass user-defined table type from VBA to SQL
body: <p>I have been tasked with creating an Excel spreadsheet as the front-end to a SQL database and some C# which performs a complicated calculation on the data. My boss wants the front end as a spreadsheet, and the calculation is seemingly too complex for VBA.</p>
  
  <p>Presently, the stored procedure to retrieve the dataset works fine. The user will then edit the data in Excel and send it back to the database. The data needs to be inserted as a new set, preserving the records of the previous sets. I have therefore written the following SQL stored procedure to complete this task:</p>
  
  <pre><code>DECLARE @Now DATETIME = GETDATE()
  DECLARE @DataSetID SMALLINT = (SELECT ISNULL(MAX([DataSetID]), 0) FROM [dbo].[tbl_DataSet]) + 1
  
  --Add DataSet entry
  INSERT INTO [dbo].[tbl_DataSet] ([DataSetID], [InputDate])
  SELECT @DataSetID, @Now
  
  --Add latest data
  INSERT INTO [dbo].[tbl_Data] ([DataSetID], [DataID], [Amount])
  SELECT @DataSetID, [DataID], [Amount]
  FROM @DataTable
  ORDER BY [DataID]
  </code></pre>
  
  <p>This means that the VBA code needs to be able to call the stored procedure, passing in a user-defined table type parameter, @DataTable AS [dbo].[typ_DataTable] READONLY.</p>
  
  <p>My present method for calling a SQL user stored procedure, passing in standard datatype parameters:</p>
  
  <pre><code>Dim cnn As ADODB.Connection
  Set cnn = New ADODB.Connection
  cnn.ConnectionString = getConnectionString()
  cnn.CursorLocation = adUseClient
  cnn.Open
  
  Dim cmd As ADODB.Command
  Set cmd = New ADODB.Command
  cmd.Parameters.Append cmd.CreateParameter("in", adInteger, adParamInput, , 1)
  cmd.Parameters.Append cmd.CreateParameter("bReturn", adBoolean, adParamOutput)
  cmd.ActiveConnection = cnn
  cmd.CommandText = ProcedureName
  cmd.CommandType = adCmdStoredProc
  
  Dim rst As ADODB.Recordset
  Set rst = New ADODB.Recordset
  Set rst = cmd.Execute()
  </code></pre>
  
  <p>Is there a simple way to pass in user-defined table types?</p>
  
  <p>Alternatively, is there an entirely better system that I can use to do this?</p>
  
  <p>The only work-arounds I can think of at the moment is to pass the data to C#, then send to the database using:</p>
  
  <pre><code>command.Parameters.Add(new SqlParameter {
      ParameterName = "@DataTable",
      Value = dataTable,
      TypeName = "[dbo].[typ_DataTable]",
      SqlDbType = SqlDbType.Structured }); 
  </code></pre>
  
  <p>However, this seems a little circuitous.</p>
  
  <p>Thanks.</p>
  
  <p><strong>Update:</strong></p>
  
  <p>As there is already a pipeline from C# to Excel, it seems best to utilise this one nasty bit of code as opposed to introducing a second with Excel &lt;-> SQL. C# can also be easily used to convert the data types and validate any parameters or return variables. Using pseudo-asynchronous methods with VBA to C# will also improve the Excel interface, preventing hanging on the UI thread.</p>