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.
  1. CREATE TABLE usertable
  2. (
  3. userid INT PRIMARY KEY,
  4. username VARCHAR(30)
  5. )
Create a table valued type
  1. CREATE TYPE username AS TABLE
  2. (
  3. user_name VARCHAR(30)
  4. )
Create a stored procedure.
  1. create proc search
  2. @user username readonly
  3. as
  4. begin
  5. select * from @user where username in(select username from usertable)
  6. 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.
Learn more here, Table Valued Parameters In SQL Server