The easiest way to do that is by using the user-defined table type feature in SQL. Parameters now may be of type 'Table'. This makes it easier for a developer to use it either in SQL or in Visual Studio.

Say you create the following SQL user-defined table type:

CREATE TYPE dbo.typTableType AS TABLE

(

[Id] INT NOT NULL,

[Description] VARCHAR(50) NOT NULL

)

If you use it in stored procedure, you have the following code:

CREATE PROCEDURE dbo.usp_Insert

@ParTable as typTableType READONLY

AS

BEGIN

-- Inside the procedure you use that parameter as a regular table with a cursor

-- or any SELECT, or in an UPDATE statement in the FROM clause, etc.

-- SQL code...

END

The following is a Visual Basic example that uses the benefit of the table type:

Let's assume you have defined somewhere in the code a datatable named datMyTable

DIM conMyConnection as new SqlClient.SqlConnection("... connection string ...")

DIM comMyCommand as new SQLClient.SqlCommand("dbo.usp_Insert", conMyConnection)

DIM parMyTable as new SqlParameter("@ParTable", SqlDbType.Structured)

parMyTable.TypeName = "dbo.typTableType"

parMyTable.Value = datMyTable

With comMyCommand

.Parameters.Add(@ParTable)

.CommandType = CommandType.StoredProcedure

.Connection.Open

.ExecuteNonQuery

.Connection.Close

End With

' rest of the code...

Table type parameters must be declared as READONLY in the stored procedure's definition.

I think this is an easier way to work with multiple records passed to SQL.