How to Call Stored Proc During Update Operation

See more:

SQL-Server-2005

SQL-Server

Hi all,

I'm new to SQL Database.
I'm now extending existing project with MS SQL server 2005.
The problem is that.
There is one existing table called: tblWorkerCertification
I'm now creating new table called: tblCourseParticipant
Bcoz of existing table and modules, I wasn't allowed to change the existing table and columns.
After the user assign the worker into course, I update table tblCourseParticipant and
I also need to insert new record into tblWorkerCertification.
We already have existing stored proc to insert new record for tblWorkerCertification called: tblWorkerCertificatipn_Sp_Insert.
So, I'm writing my stored proc to update tblCourseParticipant and
During my update process, I need to call existing tblWorkerCertificatipn_Sp_Insert stored procedure.

UPDATE  tblCPar
                SET
                        [CourseScheduleID]  = @pBIntCourseScheduleID,
                        [IsApproved]        =   @pBitIsApprove,
                        [ApprovedDate]      =   GETDATE(),
                        [ApprovedBy]        =   @pIntApprovedBy
                FROM    tblCourseParticipant tblCPar
                INNER   JOIN
                        @pXmlParticipantList.nodes ('/ParticipantList/Participant') XParTbl(XParRow)
                ON      tblCPar.[ParticipantID] = XParRow.value('@ParticipantID', 'BIGINT')
                WHERE   tblCPar.[Status] <> @pBitIsApprove

This is my stored proc for updating the tblCourseParticipant, all the selected workers will be sent with XML format. including their ID.

Where should I called this and How can I do it without using Loop?