Databases
StackOverflow2013
Postsdbo
POInserting data into one table then multiple rows into another using data from the first using stored procedures (SQL + C#)
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POInserting data into one table then multiple rows into another using data from the first using stored procedures (SQL + C#)
    text
    copied!<p>Ok so I am new here :) I am relatively new to SQL, and I am trying to insert data into multiple tables. I have both inserts to work however I want it so if one fails neither are committed. The tables look like this:</p> <p>Student - StudentID - int PK, StudentName - Varchar, etc ...</p> <p>Class - ClassID - int PK, ClassName - varchar, etc...</p> <p>StudentClass - StudentID, ClassID,</p> <p>What I am trying to do is create a new Student whom can belong to multiple classes. So I have created the Student class table to break up the many-many relationship. I have a stored procedure to insert a new student and return the newest StudentID and then I use this StudentID, in a new stored procedure, and a table value parameter to insert multiple rows into StudentClass table. These are the stored procedures:</p> <p>Create A Student:</p> <pre><code>@FirstName varchar(20) = '', @LastName varchar(20) = '', @PredictedGrade char(1) = '', @ActionPlan bit = 0, @StudentActive bit = 1, @StudentID int out INSERT INTO Student (FirstName, LastName, PredictedGrade, ActionPlan, StudentActive) VALUES (@FirstName, @LastName, @PredictedGrade, @ActionPlan, @StudentActive) SET @StudentID = SCOPE_IDENTITY() </code></pre> <p>Add Multiple Rows To StudentClass Table:</p> <pre><code>(@StudentClassCollection As InsertStudentClass READONLY) INSERT INTO StudentClass(StudentID, ClassID) SELECT StudentID, ClassID FROM @StudentClassCollection </code></pre> <p>So both of these work however I don't know how to make it so if one fails the other will not execute and changes will not be committed? So effectively I need to perform both actions one after the other in the same stored procedure? I think! As I said I am new so if I have done anything wrong please let me know I will correct it :)</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload