Executing N procedures in 1 Round trip

w/ SqlServer, is there anyway to pack a number of calls to the same stored procedure into a single round-trip to the DB short of dynamically writing a T-SQL block? For example, if I'm calling a procedure "Update Contact" which takes 2 params @.Campaign, @.Contact 20 times how would I pass in the values for those 20 diffrent versions?

You could pass all the parameters to another stored procedure as a delimited list and parse them over there, create a loop, and call the stored proc in the loop.

|||I'm sure I could. I just thought I'd heard of something similar to ODP.Net's ArrayBinding syntax for SqlServer. I'm fairly sure I wasnt thinking about bulkcopy.|||

To my knowledge I dont think there is any standardized way to make multiple calls in one trip. Perhaps someone else here knows if there's any new feature in 2005. I havent been doing much development in 2005.

|||hrm...

Will the SQL Server provider let me execute blocks of t-sql?

eg something like:

AddContact(@.C1, @.U1);
AddContact(@.C2, @.U2);
AddContact(@.C3, @.U3);
...
AddContact(@.CN, @.UN);
go;|||

Yes, however, you must specify that the call type is text, not stored procedure, and you must properly format your calls like:

EXECUTE AddContact @.C1,@.U1
EXECUTE AddContact @.C2,@.U2
...
GO

|||So what is the "proper" format for sp calls in a 'anonymous block'.