Home and Learn: Intermediate Programming
What we'll do now is to add an update button. When the button is clicked, it will update the Students table. (It doesn't make sense to update both our tables because the Courses table is the Many table in a One-to-Many relationship. We'll just be updating a Student's name, which is in the Students table.)
We'll need two more Stored Procedures. One is a simple on that grabs all the records from Students. The second one will do the updating.
In the Solution Explorer on the left, create a new Stored Procedure just like you did before. Change default file to this:
CREATE PROCEDURE [dbo].[AllStudents]
AS
SELECT * FROM Students
RETURN 0
This is a simple SELECT statement that gets all the records from the Students table.
Click the Update button. In the Solution Explorer, Refresh to see your new Stored Procedure in the folder. (You might need to refresh a few times, as it's sometimes slow to appear.) You can right-click Stored Procedure and select Execute, if you want to test it out.
The second Stored Procedure is a more difficult one. Here it is for you to create (you don't have to do all the indenting, but it does make it more readable):
CREATE PROCEDURE [dbo].[UpdateStudentsTable]
(@sID int = 0,
@Fam nvarchar(50),
@Giv nvarchar(50))
AS
BEGIN
UPDATE Students
SET FamilyName = @Fam,
GivenName = @Giv
WHERE StudentID = @sID
END
RETURN 0
The name of the Stored Procedure is UpdateStudentsTable. It has three parameters, @sID, @Fam, and @Giv. The last two are set up as nvchar(50) as that's what their Data Types were set up as when we created this table.
After the AS keyword, we have a BEGIN and END block. The update code goes inside the two. It starts with the keyword UPDATE. After a space, you need the name of a table. You SET values for columns in your table. We have a column called FamilyName and this is being assigned the @Fam parameter. We have another column called GivenName and this gets the @Giv parameter. But we only want to update these two columns WHERE we have a match for the StudentID.
Click the update button in the top left of your Stored Procedure.
To test it out, create a new Sql Query. (Right-click the Tables item in the Server Explorer then select New Query from the menu.) Try this as the query:
EXEC UpdateStudentsTable 10, "Carn", "Ken"
Click the green button to run the query. To see the results, right-click your AllStudents query and select Execute from the menu. You should see that the student called Kenny Carney has been changed to Ken Carn.
Change it back with this:
EXEC UpdateTest 10, "Carney", "Kenny"
Incidentally, you can update more than one table at a time. Just add another UPDATE:
BEGIN
UPDATE Students
SET FamilyName = @Fam,
GivenName = @Giv
WHERE StudentID = @sID
UPDATE Another_Table
SET ColumnA = @param4,
ColumnB = @param5
WHERE ColumnC = @param6
END
OK, now we have our Stored Procedures we can write some code.
Code to Update the Database Table >>
Back to the Intermediate Programming Contents Page
Email us: enquiry at homeandlearn.co.uk