How to pass table valued parameters into stored procedure from excel vba

Question

Please suggest how to pass table valued parameters into sql server procedures from excel vba.

Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim constring As String
conn.ConnectionString="My Connection String"
conn.Open
With cmd
.ActiveConnection = conn
.CommandType = adCmdText
.CommandText = "declare @tbl tbl insert into @tblvalues('fdsdf',15)"
.Execute
End With

my table type is as follows:

CREATE TYPE [tbl] AS TABLE 
(
myNO VARCHAR(10),
myVALUE FLOAT
)

my Procdure is as follows:

create procedure mydata
(
@tbl tbl readonly
)
as 
select * into #tbl from @tbl
select * from #tbl
end

Now I want to execute the procedure by taking the input of this @tbl

With cmd
.ActiveConnection = conn
.CommandType = adCmdStoredProc
**param = .CreateParameter("@po", adUserDefined, adParamInput)
.Parameters("@po").Value = param**
.CommandText = "mydata"
Set rs = .Execute
End With

Please suggest I am getting error at .Parameters("@po").Value =

Thanks

Incorrect Parameter is the error Please suggest any correction in code is required — Bhushan
– Bhushan, Commented Aug 25, 2018 at 6:58
Neither of those address how to do table valued parameters in VBA. In .NET, you would pass a DataTable, but that does object not exist in VBA. I have not determined how to do this yet either. — Shaggie
– Shaggie, Commented Jul 29, 2020 at 15:49

Shaggie · Accepted Answer · 2022-05-11 19:50:12Z

From what I can tell, adodb, which is what is supported in Excel VBA, does not support table valued parameters. See http://www.sommarskog.se/arrays-in-sql-2008.html#ADO

The workaround would be to either create a wrapping Stored Procedure that transforms some other data structure, such as sending a comma/semicolon separated list, or XML data and parsing it into a table variable, or sending a command batch to build the table variable, or, with newer SQL Server versions, using a json string with OPENJSON. http://www.sommarskog.se/arrays-in-sql-2008.html#Workarounds (does not mention json)

The examples given there are the following. www.sommarskog.se has no examples for using XML or json at the original time of posting.

Stored Procedure (Note that you would call "get_product_names_wrapper" in this example):

CREATE PROCEDURE get_product_names_wrapper @prodids nvarchar(MAX)
DECLARE @prodid_table integer_list_tbltype
INSERT @prodid_table(n)
    SELECT number FROM intlist_to_tbl(@prodids)
EXEC get_product_names @prodid_table

Parameterised command batch:

DECLARE @prodid_table integer_list_tbltype
INSERT @prodid_table(n)
    SELECT number FROM iter_intlist_to_tbl(?)
EXEC get_product_names @prodid_table

I would also suggest that you could send a dynamic command batch. It would mean that the execution plan would be recreated each time, but it would eliminate issues where your delimiter is also in your data if you were sending a list of strings. It can also more easily be expanded to address sending multiple columns of data. Note that you could expand this to using parameters instead of hard coding the data, however, it would still be dynamic in many cases assuming the number of rows is not consistent between each call. In addition, this can cause issues such as query plan cache bloat, and SQL injection concerns (when dealing with strings). Note that SQL injection can be eliminated if you handle the data being pushed into the SQL command correctly. (See arrays in sql 2005 link below)

This example is not demonstrating string data or multiple columns. It is simply following the pattern from the previous examples.

DECLARE @prodid_table integer_list_tbltype
INSERT @prodid_table(n)
VALUES (1), (22), (45)
EXEC get_product_names @prodid_table

Another option would be to create and fill a temporary table and then push its contents to the stored procedure. This would eliminate issues with the query plan cache and SQL injection in this batch, but requires extra code before this to create and fill the temp table.

DECLARE @prodid_table integer_list_tbltype
INSERT @prodid_table(n)
SELECT ProdID FROM #tmpProdIDList
EXEC get_product_names @prodid_table

Note that this article is a good read that provides all of your options and the limitations of some of these: www.sommarskog.se/arrays-in-sql-2005.html.

That article mentions SQL injection being a concern for some options, but does not state that if you handle it correctly, then it is not a problem. Also, where it mentions Dynamic SQL, it is referring to inside the stored procedure. To eliminate the SQL injection there would require that you parse the passed in string value, breaking it apart, and combining it as needed, ensuring that extra commands were not "injected" into the data. I am not typically concerned about SQL injection in stored procedures since I have limited control there and want them to run as efficiently as possible, so I do not write them to allow this. Instead, my concern is in any dynamic SQL that I write.

I would therefore not write this (CustomerList is typically something like " 'Franks', 'Johns', 'Toms' "):

rs.Open "SELECT * FROM Customers WHERE CompanyName IN (" & CustomerList & ")", cn

Instead I would write this (assumes CustomerList has data/is defined. it is an array like {Franks, Johns, Toms})

For Index = 0 To UBound(CustomerList)
    Customers = Customers & "'" & Replace(CustomerList(Index), "'", "''") & "',"
Next
Customers = Left$(Customers, Len(Customers) - 1)
rs.Open "SELECT * FROM Customers WHERE CompanyName IN (" & Customers & ")", cn

If you are using a newer version of SQL Server you can now use OPENJSON to transform a json string into a rowset, which is likely the best way to handle this to date. You can google many examples for this, however you would have to create your own json string in Excel. This would be a trivial matter for simple data. https://learn.microsoft.com/en-us/sql/relational-databases/json/convert-json-data-to-rows-and-columns-with-openjson-sql-server?view=sql-server-2016

Collectives™ on Stack Overflow

How to pass table valued parameters into stored procedure from excel vba

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related