Not sure if all the syntax below works with SQL2000.
The solution is to pass a list of Student Names as XML into the Stored Procedure, and update the INSERT statement to insert multiple records.
DECLARE @students TABLE (st_name VARCHAR(50));
DECLARE @students_xml XML;
INSERT @students
SELECT 'Michael'
UNION
SELECT 'Bob'
SET @students_xml =
(
SELECT *
FROM @students AS student
FOR XML AUTO, ROOT('students')
);
SELECT
Tbl.Col.value('@st_name', 'varchar(50)') AS st_name
FROM @students_xml.nodes('//students/student') Tbl(Col)
Also, if you are using SQL 2008 and above, you can use
Table-Valued parameter.