Search Table Valued Parameter In SQL Server

Here is a SQL query uses table valued parameter in SQL Server to search within a SP.

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.

CREATE TABLE usertable
(
userid INT PRIMARY KEY,
username VARCHAR(30)
)

Create a table valued type 

CREATE TYPE username AS TABLE
(
user_name VARCHAR(30)
)

Create a stored procedure. 

create proc search
@user username readonly
as
begin
select * from @user where 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.

 

Learn more here, Table Valued Parameters In SQL Server