Passing a Table Valued parameter to a query


I have been experimenting with passing a datatable to a SQL query to compare a list of names with the person table.

It doesn’t work however and most of the articles and forum posts I’ve read all mention stored procedures. Can you only use table valued parameters with a stored procedure?

I’ve matched my column name up with the column in the User Defined Table Types, and set the parameter to use the structured SQLDBType.

The command sent off to the server is:

Select * From person where person.Person_Name In ((select * from @par))

Table name is person Column name is Person_Name

@Par is created in vb. Net via:

Par = New SQLParameter(“@Par”,SqlDbType.Structured) Par.Value = dt Par.TypeName = “compareTable”

The parameter is added to the sqlcommand.

The User Defined Table Type is called “compareTable” in the SQLDatabase

Everything executes fine. I just don’t get any rows returned correctly. If I change the IN to select Person_Name From Person, I get all the expected rows.

Any ideas?

submitted by /u/rosscrooks83
[link] [comments]

Leave a Reply