Update Transaction Dates @Table Param = @New Table Param --Select from the table to verify the change SELECT * FROM Trans Log Note: The storedprocedure_is the insert procedure that passes the table-valued parameter with the matthewharrisapplication. The code at callout C creates a parse CSV as a public list.Book3.csv, which you can download, is an example comma-delimited file of the values that will be passed with the stored procedure. The code at callout D adds functionality to the Browse button.Basically, a table-valued parameter lets you use an array of data in T-SQL, as well as send an entire data-set table as a parameter in a stored procedure or function.SQL Server 2008 stores this parameter as a user-defined table type.
Second, you create a stored procedure with a table-valued parameter, as Listing 2 shows.
You can also add primary key and unique constraints to the table type.
The ddl_in Listing 1 sets up the test database Movie Rental DB with a table and the table type. Listing 1: Code to Populate Movie Rental DB with a Table and Table Type CREATE DATABASE \[Movie Rental DB\] USE Movie Rental DB CREATE TABLE Trans Log( Trans ID int IDENTITY(101,1) PRIMARY KEY not null, User ID int not null, Trans Date date not null, Type ID int not null) CREATE TYPE dbo.
If not (if the database is created on a different instance of SQL Server than local server), SQLConnection will have to be changed. The following brief procedure will help you test functionality.
It would be difficult to add thousands of records to the table type with the code above because I would have to use a T-SQL INSERT statement to insert each of the records. NET application would be difficult, but as I learned and as you’ll see, it involves few steps and very few lines of C# code (I used the 3.5 Framework, which isn’t necessary but is recommended.) I’ll create an application that parses the data from a file into a Data Grid View, sets the Data Grid View as a Data Table, and passes the Data Table as the table parameter. First, click Browse to find the file, as shown in Figure 3.
Finally, use the following code to verify that the values have been updated in the database. As you can see, it’s quite straightforward to use a .