How can we insert multiple records using a stored procedure in MySQL?

Question

Could someone tell me how can we insert multiple records using a stored procedure in MySQL?

Answer 1

 

CREATE PROCEDURE CompanyEmployee

  @EmployeeName NVARCHAR(50),

  @Students XML

AS

BEGIN

    DECLARE @StudenTable TABLE(Name NVARCHAR(50))

    DECLARE @EmployeeId INT

 

    INSERT INTO @StudenTable

    SELECT Tbl.Col.value('@Name', 'NVARCHAR(50)')

    FROM @Students.nodes('//Student') Tbl(Col)

 

    INSERT INTO CompanyEmployee VALUES(@EmployeeName)

    SET @EmployeeId = SCOPE_IDENTITY()

 

    INSERT INTO Student 

    SELECT @EmployeeId, Name FROM @StudenTable

END