Post
Ask Question

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.
  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.