Search Table Valued Parameter In SQL Server

Table Valued Parameter helps us to pass multiple rows of data from a client application to SQL Server without multiple round trips. We can pass multiple rows to a stored procedure using a Table Valued Parameter.

This SQL query shows how to search in a collection of data returned by a stored procedure using table valued parameters.

Create a table and add some data to it. If you've your own table, use that.

CREATETABLE usertable

(

userid INTPRIMARYKEY,

username VARCHAR(30)

)

Create a table valued type

CREATE TYPE username ASTABLE

(

user_name VARCHAR(30)

)

Create a stored procedure.

create proc search

@user username readonly

as

begin

select * from @userwhere username in(select username from usertable)

end

Now you can use this procedure in your C# code when you want to filter a users list that you have in a datatable and need to get all users who are in user table.