Home and Learn: Intermediate Programming
Using Sql Parameters in Stored Procedures
Create a new Stored Procedure, just like you did last time. Change the name to GetStudentByID:
CREATE PROCEDURE [dbo].[GetStudentByID]
What we want to do in our C# code is to have a user type a Student ID in a text box and then search the database for a student that matches the ID. We can use the same SELECT statement we had before:
SELECT Students.GivenName, Students.FamilyName,
Course.Course
FROM Students
JOIN Course
ON Students.StudentID = Course.StudentID
You've met WHERE clauses in a previous lesson. We can add one here, as well. If you were hard-coding the WHERE clause, it would look something like this:
WHERE Students.StudentID = 10
You can replace that hard-coded 10 with a parameter. By default, SQL Server gives you two parameters:
@param1 int = 0,
@param2 int
Parameters are placeholders for values. In our example, you can replace to hard-coded 10 with your parameter:
WHERE Students.StudentID = @param1
You'll see in a moment how to execute a statement in SQL Server. But you pass a value over and it ends up in the placeholder, @param1. This will happen in our C# code, as well: whatever student id number a user types into a text box will end up in @param1.
Except, param1 is just a default name for the parameter. You can have almost anything you like here. Change param1 to something more appropriate. Call it GetStudentID. And you can delete the second parameter as we'll have just the one. If you wanted to, you could use a second placeholder. For example, suppose you wanted to search on the StudentID and the FamilyName. You could add an AND part to the WHERE clause:
WHERE Students.StudentID = @param1 AND FamilyName = 'Carney'
But delete the second parameter and the top of your Stored Procedure will look like this: (Don't worry about the underlines):
CREATE PROCEDURE [dbo].[GetStudentByID]
@GetStudentID int = 0
AS
The int after the parameter is a type, and it's just like setting up a variable in C# - you need to specify the type of value going into your parameter. But the types here are the same ones from the dropdown list you used when setting up your table, the ones with NVARCHAR. These ones:
We set up the StudentID column to be an int, so the parameter needs to be an int as well. Had we set it up as NVARVCHAR our parameter type would be this:
@GetStudentID NVARCHAR(50)
The = 0 on the end means you're setting a default value for your parameter.
You can paste in your SELECT statement, the one you had before. This one:
SELECT Students.GivenName, Students.FamilyName,
Course.Course
FROM Students
JOIN Course
ON Students.StudentID = Course.StudentID
Paste it in after the AS keyword and before the RETURN 0 at the end. Add the WHERE clause, as well:
SELECT Students.GivenName, Students.FamilyName,
Course.Course
FROM Students
JOIN Course
ON Students.StudentID = Course.StudentID
WHERE Students.StudentID = @GetStudentID
Your Stored Procedure window should look like this:
Click the Update button in the top left when you're done. In the Server Explorer area on the left, click the Refresh button again. You should see your second Stored Procedure in the folder:
Let's test it out.
Right-click on the Tables folder. Select New Query from the menu. To execute a Stored Procedure, you ned this format:
EXEC [Stored_Procedure_Name] [Any_Parameters here]
Type this in the Query window:
EXEC dbo.GetStudentByID 10
Click the Green button to run the query. You should see three results displayed:
Change the 10 into 11 and run the query again. You should see the results for Bob Job appear. Try 12 to see Mona Lisa's result.
OK, now that we have a database, two tables, and some Stored Procedures, we can do the C# coding.
Back to the Intermediate Programming Contents Page