How to pass values from VBA to a table variable in SQL Server 2008?

Question

I have a user form in Excel 2007 that writes data into a table on SQL Server 2008 using a stored procedure.

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.

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.

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.

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.

Thanks for your help!!!

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

Here is my VBA code so far.

--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 = " & 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 " & _
                     "Values (" & Incident_Number.Value & ",'" & .List(I - 1, 0) & _
                     "','" & .List(I - 1, 2) & "')"
        rst.Open SQL_String, cnn, adOpenStatic
        Set rst = Nothing
    Next I
End With`

Not sure what you're after but I have noticed that the datatypes defined in your ADO Parameters are different from the Stored Proc parameters. I've had issues in the past with such things, so something to be aware of (if it's not already causing you problems). This might help: w3schools.com/ado/ado_datatypes.asp — markblandford
– markblandford, Commented Apr 24, 2012 at 10:44
Hey, yes, that might be. I'll harmonize them but how can I pass several values from vba into a table variable in SQL. I have no idea where to start. — Daniel
– Daniel, Commented Apr 27, 2012 at 0:41

markblandford · Accepted Answer · 2012-04-27 07:45:09Z

1

I don't think this is possible with normal / classic ADO as there is no Table Variable Datatype.

An alternative could be to build an XML data string and then parse it in your stored proc.

answered Apr 27, 2012 at 7:45

markblandford

3,2133 gold badges22 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to pass values from VBA to a table variable in SQL Server 2008?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related