Here's how to use table-valued parameters. (above solutions can work as well)
1. Create a table type in your database:
CREATE TYPE CardTableType AS TABLE
( Card VARCHAR(50) )
2. You create a stored procedure that accepts table-valued parameter:
CREATE PROCEDURE dbo. usp_GetCards
@Cards CardTableType READONLY
AS
SET NOCOUNT ON
Select ... Where Card in (Select card From @Cards)..
3. In your .net code:
param = new SqlParameter("@Cards", dataTableCardType);
param.TypeName = "CardTableType";
param.SqlDbType = SqlDbType.Structured;
cmd.Parameters.Add(param);
For more information:
Use Table-Valued Parameters (Database Engine)[
^]