Sometimes it is needed to pass comma separated values to sp and then we extract it. But instead of that we can pass a xml string to sp and convert it to temp table. Here is how :
In DAL :
string XMLEntityIDS = “<Root>”;
foreach (int Entity in lstNewEntities)
{
XMLEntityIDS += “<EntityIDS EntityID=\”" + Entity.ToString() + “\” ></EntityIDS>”;
}
XMLEntityIDS += “</Root>”;
db.AddInParameter(cmdUpdateOrder, “@EntityIDs”, DbType.String, XMLEntityIDS);
In SP :
DECLARE @docHandle int
EXEC sp_xml_preparedocument @docHandle OUTPUT, @EntityIDs
SELECT * Into #TempEntities FROM OPENXML(@docHandle, N’/Root/EntityIDS’) WITH (EntityID int)
