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 ...);

TomTom · Accepted Answer · 2010-10-27 11:55:38Z

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.

answered Oct 27, 2010 at 11:55

TomTom

62.4k11 gold badges95 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Patrice Pezillier Over a year ago

Ok. Performance are very very very bad when I do the loop in c# comparing to the same kind of loop in T-SQL.

Lieven Keersmaekers Over a year ago

As TomTom said, convert the list of objects to a TABLE. Look for passing XML parameters and how to use them as a table in SQL Server.

JeffO Over a year ago

If you're talking about doing a cursor loop in t-sql, don't expect any type of performance advantage.

Slappy · Accepted Answer · 2010-10-27 23:16:38Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related