How can I pass a list of object in a SQL Procedure with SQL Server 2008 ?

Question

How can I pass a list of object in a SQL Procedure with SQL Server 2008 ?

I want to avoid this :

foreach (var item in itemList)
{
   myContext.ExecuteStoreCommand("EXEC MyAtomicProc ... item ...);
}

I want to do the foreach in a store procedure.

How can I pass a c# list of object and execute this procedure in EF :

myContext.ExecuteStoreCommand("EXEC MyGlobalProc ... itemList ...);

Answer 1

You can not. You can:

• Provide scalar values (variables)
• Provide a TABLE of values

But you CAN NOT pass in objects. Objects do not exist at the SQL layer in a way that is compatible with the .NET runtime.

Answer 2

What I like to do is serialize the list of objects into a string, then pass that as a DB parameter.

Then in the stored proc you can use XML transform to create a temp table that you can join in onto. This lets you easily do bulk updates and inserts (using left join)

This converts a list of key value pairs to a temp table. You can extract it to a list of anything really.:

DECLARE @xmlHandle INT 
DECLARE @pixelParametersTable TABLE (
 [Key] VARCHAR(10) PRIMARY KEY, 
 [Value] VARCHAR(MAX)) 
EXEC sp_xml_preparedocument @xmlHandle output, @pixelParameters

INSERT INTO @pixelParametersTable 
SELECT [Key], [Value]
FROM  OPENXML (@xmlHandle, '/PixelRequestParameters/Parameter',1)
WITH (  
 [Key] VARCHAR(10) 'Key',
 [Value] VARCHAR(8000) 'Value'
) 
EXEC sp_xml_removedocument @xmlHandle