How to generate multiple rows in a stored procedure?-CodePudding

I would like to know how to create a stored procedure that will accept multiple values in a parameter and return multiple rows.

This will be my parameters, having classId accept multiple values and insert multiple rows with same student name:

@studentName AS NVARCHAR(255) = NULL,
@classId AS INTEGER = NULL

INSERT INTO studentClass (classId, studentName)
VALUES (@classId, @studentName)

Example John Doe is registered to class 1 and 2, I want to add Jane Doe to class 1 and 3. How do I do that in a single run?

classId | studentName
-------- ------------
   1    | John Doe
   2    | John Doe

Run stored procedure with parameters:

@studentName = Jane Doe
@classId = 1,3

and will result into this

classId | studentName
-------- ------------
   1    | John Doe
   2    | John Doe
   1    | Jane Doe
   3    | Jane Doe

CodePudding user response：

Like Dale mentioned in the comments, you can use table valued parameters with user-defined table types to create a parameter in your procedure that accepts a table of data at a time.

E.g. first:

-- Create user-defined table type
CREATE TYPE ClassIdsTable AS TABLE (ClassId INT);

Then:

-- Create procedure with table valued parameter
CREATE PROCEDURE dbo.InsertStudentClasses
    @StudentName NVARCHAR(255),
    @ClassIds ClassIdsTable
AS

-- Insert into studentClass table 
INSERT INTO studentClass (classId, studentName)
SELECT ClassId, @StudentName
FROM @ClassIds;

-- Return results from procedure 
SELECT ClassId, @StudentName
FROM @ClassIds;

Then you can call your procedure like so:

DECLARE @ClassIdsTable AS ClassIdsTable;
INSERT INTO @ClassIdsTable
VALUES (1), (3);

EXEC dbo.InsertStudentClasses 
    @StudentName = 'Jane Doe',
    @ClassIds = @ClassIdsTable;

Alternatively, if you're using SQL Server 2016 or later, you can also create the @ClassIds parameter as a string-based data type, and send in a comma delimited list of IDs instead. Then you can use the STRING_SPLIT() function to convert it to a table of values. But it's a little anti-patternish to store multiple data points in a single string and has drawbacks.