title: How to pass values from VBA to a table variable in SQL Server 2008?
body: <p>I have a user form in Excel 2007 that writes data into a table on SQL Server 2008 using a stored procedure.</p>
  
  <p>The inserted row then gets an Identity which I report back to VBA. I then use this number to populate several rows of data into another table.</p>
  
  <p>However, instead of having the stored procedure executed first and then writing into another table, I want to have the stored procedure do both things. </p>
  
  <p>Problem is, that the entry for the other table consists is bigger than one row and the parameter that I send from VBA to SQL can only contain one value.</p>
  
  <p>So, I believe I need to create a table variable in SQL. However, how do I pass the values from VBA over to my table variable in SQL? Remember, everything should be done in one stored procedure. This is what I have so far. </p>
  
  <p>Thanks for your help!!!</p>
  
  <pre><code>--SQL Stored Procedure
  ALTER procedure [dbo].[Audit_InsertAuditFinding]
  
   @AuditRID as int 
  ,@EnteredBy as varchar(10)  
  ,@AuditItemNumber as int 
  ,@AuditFindingShort as varchar(50) 
  ,@AuditFindingLong as varchar(500)  
  ,@AuditDocsSaved as bit
  ,@AuditIncident as int output
  
  as
  BEGIN
  SET NOCOUNT ON
  Insert Into Audit_Findings 
  Values (@AuditRID,@EnteredBy,GETDATE(),@AuditItemNumber,@AuditFindingShort,@AuditFindingLong,@AuditDocsSaved)
  
  declare @AuditFindingNumber as int
  select @AuditFindingNumber = MAX(@@IDENTITY)
  
  select @AuditIncident = @AuditFindingNumber
  END
  </code></pre>
  
  <p>Here is my VBA code so far.</p>
  
  <pre><code>--VBA Code
  cnn.CursorLocation = adUseClient
  cmd.ActiveConnection = cnn
  cmd.CommandType = adCmdStoredProc
  cmd.CommandText = "InternalReporting.dbo.Audit_InsertAuditFinding"
  cmd.NamedParameters = True
  Set prm = cmd.CreateParameter("@AuditRID", adInteger, adParamInput, 50, Audit_Topic.Value * 1)
  cmd.Parameters.Append prm
  Set prm = cmd.CreateParameter("@EnteredBy", adVarWChar, adParamInput, 10, Environ("UserName"))
  cmd.Parameters.Append prm
  Set prm = cmd.CreateParameter("@AuditItemNumber", adInteger, adParamInput, 10, Audit_ItemNumber.Value * 1)
  cmd.Parameters.Append prm
  Set prm = cmd.CreateParameter("@AuditFindingShort", adVarWChar, adParamInput, 50, Audit_ShortDescr.Value)
  cmd.Parameters.Append prm
  Set prm = cmd.CreateParameter("@AuditFindingLong", adVarWChar, adParamInput, 500, Audit_LongDescr.Value)
  cmd.Parameters.Append prm
  Set prm = cmd.CreateParameter("@AuditDocsSaved", adVarWChar, adParamInput, 500, Doc_Saved)
  cmd.Parameters.Append prm
  Set prm = cmd.CreateParameter("@AuditIncident", adVarWChar, adParamOutput, 10, Incident_Number.Value)
  cmd.Parameters.Append prm
  
  cmd.Execute
  
  Incident_Number.Value = cmd.Parameters("@AuditIncident") * 1
  SQL_String = "Delete from InternalReporting.dbo.Audit_Findings_Notifications where Audit_Incident = " &amp; Incident_Number.Value * 1
  
  rst.Open SQL_String, cnn, adOpenStatic
     Set rst = Nothing
  
     With AUD_02_Item_Mgmt.Notify_Overview
      For I = 1 To .ListCount
          SQL_String = "Insert Into InternalReporting.dbo.Audit_Findings_Notifications " &amp; _
                       "Values (" &amp; Incident_Number.Value &amp; ",'" &amp; .List(I - 1, 0) &amp; _
                       "','" &amp; .List(I - 1, 2) &amp; "')"
          rst.Open SQL_String, cnn, adOpenStatic
          Set rst = Nothing
      Next I
  End With`
  </code></pre>