Bulk Searching using Table Valued Parameter in SQL Server 2008

Bulk searching using Table valued parameter in SQL Server 2008.

Follow following Steps.

Create a table:

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.