We often get requirements from customers that multiple rows needs to be edited and saved at once to the database . As in the example below: 

Suppose the rows in the screen shot below need to be updated at once in a single transaction and a single update statement without multiple updates .

 

 

One way of achieving this is using the xml datatype parameter in a Stored Procedure.We can form a xml document in the code and pass it to a stored procedure

Exec UpdateStudentpercentage

 '<ID><StudentID>1</StudentID><Percentage>60</Percentage></ID>

 <ID><StudentID>2</StudentID><Percentage>70</Percentage></ID>

 <ID><StudentID>3</StudentID><Percentage>90</Percentage></ID>

 <ID><StudentID>4</StudentID><Percentage>100</Percentage></ID>

 <ID><StudentID>5</StudentID><Percentage>40</Percentage></ID>

 <ID><StudentID>6</StudentID><Percentage>60</Percentage></ID> 

 <ID><StudentID>7</StudentID><Percentage>35</Percentage></ID>'

 

 

And the xml document can be loaded from the stored procedure and updated using a single update statement as in the following code snippet.
Create Procedure [dbo].[UpdateStudentPercentage]
@values Xml
as
Begin

    Declare @tabvar table
    ( StudentId int , Percentage int )
    Insert Into @tabvar ( StudentId , Percentage )
    Select t.value ( 'StudentID[1]','int' ) As ID , t.value ( 'Percentage[1]','float') As Per
    From @values.nodes('/ID') As T (t)
 
   Declare @count int = @@ROWCOUNT
 
      Update Student
       set Percentage = t.Percentage
        From @tabvar t Left Join Student s on (t.StudentId = s.StudentID)
        
End