Use it for SQL schema generation using Add Generated items 
wizard.

Use it for Send and receive Port

If your case is the simple Add/Update-ing several rows, the 
Updategram give you more simple way. You completely were 
out of the SQL code.

SP give you more control under your data inside SQL. If you 
also have SPs or
part of your business logic is/will be inside SQL you need 
use SPs. Usually we have to use SP with OPENXML.

we will use updategram and storedprocedure when we want to 
insert any information to database or we are selecting any 
information from database

when we use the SQL Adapter for generating Schemas, Port etc.

This is way we can use updategram to update multiple rows 
in BizTalk orchestration.

Using the updategram to insert and delete are very 
straightforward. It's also easy to update one row of data 
using the updategram in BizTalk. BizTalk SQL adapter seems 
to be problematic when dealing with updating multiple rows 
of data using updategram. SQL XML states that you need to 
use the attribute "updg:id" to associate the records 
enclosed in the <updg:before> and those enclosed in 
<updg:after>