1) Create table type
itemtabletype for a table with one, unique clustered column called "item":
create type itemtabletype
as table (
item nvarchar(30) not null,
primary key clustered (item)
)
2) Create a procedure cst_testtabletype that has itemtable type as a table-valued input parameter:
create procedure cst_testtabletype
( @tv itemtabletype readonly )
as
begin
declare @itemcount int = 0
select @itemcount = count(*) from @tv
select concat('succesfully parsed item ',item) from @tv
end
The procedure returns all items provided in the table-valued parameter prefixed with the words "successfully parsed item ".
3) Give execution rights to user(s) and/or groups that are allowed to execute the procedure and table type:
grant exec on type::
itemtabletype to [User]
grant exec on
cst_testtabletype to [User]
4) Finally, create the following test routine in VB.Net:
Public Sub Start()
'create data table
Dim Table1 As DataTable
'create a table named tmptbl
Table1 = New DataTable("tmptbl")
Dim row As DataRow
Try
'declare a column named item
Dim item As DataColumn = New DataColumn("item")
'setting the datatype for the column
item.DataType = System.Type.GetType("System.String")
'adding the column to table
Table1.Columns.Add(item)
'declaring a new row
Dim i As Integer
For i = 1 To 1000
row = Table1.NewRow()
row.Item("item") = i
Table1.Rows.Add(row)
Next
Catch
End Try
'establishing connection. you need to provide password for SQL server
Dim myConnection = New SqlConnection()
myConnection.ConnectionString = "Server=mySQLServer;Database=myDB;Trusted_Connection=True;"
Try
myConnection.Open()
Dim myCommand As New SqlCommand("cst_testtabletype", myConnection)
' the table-valued parameter is called @tv for cst_testtabletype
Dim myparam As SqlParameter = myCommand.Parameters.Add("@tv", SqlDbType.Structured)
'create parameter
myCommand.CommandType = CommandType.StoredProcedure
myparam.Value = Table1
Dim dr As SqlDataReader = myCommand.ExecuteReader()
While dr.Read()
Debug.Print(dr.Item(0))
End While
Catch ex As SqlException
MsgBox(ex.Message)
End Try
End Sub
