SQL Queries, Joins, Stored Procedures

For C# and VB NET Users

You've used SQL queries in previous lessons. You selected all the record from a single table with something like this:

SELECT * FROM Students

You can try that again. In the Server Explorer window, right click on the Tables folder and Select New Query from the menu:

The Server Explorer showing a context menu with the New Query item selected

In the Query window enter the above SQL. You query window should look like this:

The Sql server query window showing a simple SELECT query

(SQL Commands are not case-sensitive, by the way. You could enter the SELECT and the FROM in lowercase, if you wanted to.)

Click on the Green Arrow in the top left to run the query. You should see all the records from the Students table displayed. Now change the Students to Course. Run the query again and you'll see all the records from the Course table displayed.

You don't have to select all the records. You can specify the columns you want by typing the table name first, then a dot. You should see a list of available columns in your table:

Specifying a column to select on the Students table

Select the column you want in your results. To add more columns, separate them with commas:

SELECT Students.GivenName, Students.FamilyName FROM Students

However, what we want to do is to display results from both tables. To do that, we need something called a JOIN.

 

Sql Server Database JOIN

If you wanted to display only certain selected columns from both tables, you could do it like this:

SELECT Students.GivenName, Students.FamilyName, Course.Course
FROM Students, Course
WHERE Students.StudentID = Course.StudentID

Here, we're selecting only three columns FROM both tables. Notice that the table names are separated by commas. (The dbo part is the schema that SQL Server gives us by default.) The WHERE clause at the end tells SQL Server to only display the records where the StudentID columns match in both tables.

In fact, try that out. Open a New Query and type the above into the window. Run your query to see the results.

There is another way to do this, though: use a JOIN.

There are a few different types of JOIN (inner, outer, cross, left, right, full) but they all work with a Primary Key in one Table and a Foreign Key in another. The difference is in how many results you want back, and whether to include NULL records or not.

The one we'll use is an INNER JOIN. The format is this:

SELECT [Columns]
FROM [Parent Table]
INNER JOIN [CHILD TABLE]
ON [Primary Key = Foreign Key]

The SQL Keywords are SELECT, FROM, INNER JOIN, ON. The parent table is the one with the StudentID Primary Key. The child table is the one with the StudentID as the Foreign Key. The INNER JOIN is ON the two.

To try it out, create a new query. Now enter the following: (You don't have to have the SQL on separate lines, but it does make it more readable.)

SELECT Students.GivenName, Students.FamilyName, Course.Course
FROM Students
INNER JOIN Course
ON Students.StudentID = Course.StudentID

Run your query and you'll see the results appear:

The results of an Inner Join query run on the two tables

You can actually miss out the INNER part at the start. So just have JOIN instead of INNER JOIN. The result is the same.

The point about learning all this SQL is that you can use it in your programming code to pull records from your database. To make your life easier as a programmer, and to help with security issues, you can create something called a Stored Procedure in your database. You can then grab your Stored Procedures with code. Let's create one.

 

Stored Procedures

To create a Stored Procedure, right-click the Stored Procedure folder in the Server Explorer. From the menu select Add New Stored Procedure:

Server Explorer showing a context menu with the Add New Stored Procedure item selected

A new window will appear with some complex-looking SQL. This:

The default Stored Procedure window in T-SQL

The CREATE line is where you can change the name of your procedure. Click into the square brackets and delete the Word Procedure. Type a new name. Type NamesAndCourses:

CREATE PROCEDURE [dbo].[ NamesAndCourses]

We'll get onto parameters shortly. For now, delete the two lines that start with @param. Leave the As keyword. In place of the SELECT line, copy and paste your Join SQL. You should now have this:

A JOIN Stored Procedure created to pull data from two tables

Click the Update button and you'll see the Preview box appear:

The Preview Database Updates dialog box

Click Update Database.

In the Server Explorer on the left click the Refresh button:

The Server Explorer showing the Stored Procedure folder selected

You should now see your new Stored Procedure in the folder:

The Stored Procedure folder with the new Procedure created

When we get to the coding section, we'll grab this Stored Procedure using its name, NamesAndCourses.

You can run this Stored Procedure. Right click it and select Execute from the menu:

Executing the Stored Procedure

You should the results of the query displayed in the bottom window and some SQL at the top. The table of results is what we're after. This is what we'll get when execute our query with code later.

Now that we have a Stored Procedure that gets all the results, let's create one to limit the results we get back. This time, we'll use those one of the @param values you deleted earlier. We'll do that in the next lesson below.

Sql Parameters >>

Back to the C# NET Contents Page

Back to the VB NET Contents Page