Making Use of SQL Variables

Introduction

Today, you will learn about SQL Variables and how to use them from Visual Basic.

The basics…

Databases

Databases can be friendly creatures if you know how to use them correctly; they can become also very unfriendly, especially if you have no idea on how to use them properly. A database’s sole function is to store information, hence the name: data base. Knowing how to properly store inside them takes practice, and lots of planning. People who fail to this get eaten up by the database monster.

A database consist of the following objects (I will not go into great detail on all of them, because this is only an introduction):

  1. Tables
  2. Queries
  3. Views
  4. Stored Procedures
  5. Functions

Tables

Tables are the main building blocks of any database. This is where all the information will be stored. A table consists of rows (records) and columns (fields). A row, or record, contains all the information pertaining to one topic. A column, or field, is once piece of information. Here is a small example of records and fields:

SQL1
Figure 1: Records and Fields

In Figure 1, you can see that there are seven records, and three fields. We will create this database a bit later.

Although you may have many columns in each table and many tables at your disposal in a database, it doesn’t mean that you have to make use of all of them. With databases, less is more. This means that the least amount of fields and the least amount of tables used in your database, will amount to the best performance. Let me give you another example:

SQL2
Figure 2: Database Tables used productively

This is from one of my programs I had to implement at my work. Its whole purpose is to track student progress according to which course the student is doing. Each course has different subjects. I show this here to explain to you that a simple program can make use of multiple inter-connected tables. An inexperienced programmer would add all the fields into one table, which is possible, but is also wrong. This system has been running successfully for twelve years already. There is more than enough space to store the student’s personal information, as well as more than enough space for custom courses and custom subjects.

All these tables are connected by a term called relationships. If you look closely at the tables inside Figure 2, you will see that inside each table there is a reference to another table’s field. This reference forms a relationship between tables. A relationship becomes possible when there is a Primary key inside one table referencing a Foreign key in another table. This is not really the main topic of today’s article, so let me not get ahead of myself (as always) again. More information on Database tables can be found here.

Queries

Now that we know where and how to store data, we need to know how to access that data. This is done through a database query. You usually have to write a query to extract the needed information from the appropriate tables. I will delve deeper into this in my next article (Extracting Data). More information on database queries can be found here.

Views

A View is a dynamic result set. This means that we can create a view based on a query and it will not affect any data that is stored. More information on Views can be found here.

Stored Procedures

A Stored Procedure is a sequence of SQL code that can perform tedious tasks quickly. By making use of Stored Procedures, you will save a lot of time and won’t need to run each query (that can reside in a Stored Procedure) manually. More information on Stored Procedures can be found here.

Functions

There are many functions inside the SQL Language. These functions can help with string manipulation, date manipulation, as well as computing Averages, just to name a few. A complete list of SQL Functions can be found here.

SQL Variables

SQL Variables serve the same purpose as Visual Basic variables: to store data, or a pointer to data. An example of an SQL Variable is as follows:

DECLARE @Date Date
   SET @Date = (SELECT CAST(GetDate() AS Date))
SELECT
   @Date AS [Date]

FROM Table

WHERE
   Condition

In the preceding example, I created a variable by using the Declare statement. I then used the Set statement to store data into the variable. Lastly, I used an ordinary Select statement to make use of the variable.

In the above example, you will also notice that I stored the result of a Select statement into the variable, you could obviously hardcode a piece of information as well, but I thought it appropriate to show a more complicated example.

Our Project

Open SQL Server and create two tables that look as follows:

SQL3
Figure 3: Table Design

Create the query in SQL Server as follows:

DECLARE @StudentNumber bigint
   SET @StudentNumber = (SELECT StudentNumber
      FROM StudentDetails WHERE Name = 'Hannes')

   DECLARE @Date Date
   SET @Date = (SELECT CAST(GetDate() AS Date))

   SELECT
   CourseCode, CourseName, @Date AS [Date]
FROM CourseDetails

WHERE
   StudentID = @StudentNumber

In the previous query, I created two variables. The first variable hosts the StudentNumber value from the StudentDetails table and the other variable stores the current date that I got by using the GetDate() SQL function.

The Select query makes use of both variables and displays the data accordingly.

Create a new Visual Basic Windows Forms project and add a DataGridView to the form plus one button. Add the following code:

Imports System.Data.SqlClient

Public Class Form1

   ' Shared variables
   Dim con As SqlConnection = _
      New SqlConnection("Data Source=.\HTG;AttachDbFilename= _
      |DataDirectory|\Students.mdf;Integrated Security=True; _
      Connect Timeout=30;User Instance=True")
   Dim cmd As SqlCommand
   Dim myDA As SqlDataAdapter
   Dim myDataSet As DataSet

   Private Sub Form1_Load(ByVal sender As System.Object, _
         ByVal e As System.EventArgs) Handles MyBase.Load
      ShowData()
   End Sub

   'Binding database table to DataGridView
   Public Sub ShowData()
      cmd = New SqlCommand("DECLARE @StudentNumber bigint _
         SET @StudentNumber = (SELECT StudentNumber _
         FROM StudentDetails WHERE Name = 'Hannes') _
         DECLARE @Date Date SET @Date = _
         (SELECT CAST(GetDate() AS Date)) _
         SELECT CourseCode, CourseName, @Date AS [Date] _
         FROM CourseDetails _
         WHERE StudentID = @StudentNumber", con)
      If con.State = ConnectionState.Closed Then con.Open()
      myDA = New SqlDataAdapter(cmd)
      myDataSet = New DataSet()
      myDA.Fill(myDataSet, "MyTable")
      DataGridView1.DataSource = _
         myDataSet.Tables("MyTable").DefaultView
   End Sub
End Class

Obviously, you must change the connection string to your database and your server. In the preceding code segments, I made use the SQLCommand object to execute the previous query. The resulting dataset gets displayed inside the DataGridView.

Conclusion

As you can see, SQL Variables aren’t difficult to use from Visual Basic. Until we meet again.

Hannes DuPreez
Hannes DuPreez
Ockert J. du Preez is a passionate coder and always willing to learn. He has written hundreds of developer articles over the years detailing his programming quests and adventures. He has written the following books: Visual Studio 2019 In-Depth (BpB Publications) JavaScript for Gurus (BpB Publications) He was the Technical Editor for Professional C++, 5th Edition (Wiley) He was a Microsoft Most Valuable Professional for .NET (2008–2017).

More by Author

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Must Read