13 Using variables

 

We’ve written and executed a lot of SQL queries so far, and a good number of those queries involved filtering the results on specific values. Through many examples, we’ve seen how to filter on a particular order or title ID, customer name, or date range, and every time, we’ve specified the literal value for filtering in our SQL. A literal value is specific, such as the number 4 or the date 2020-10-06. Using literal values is helpful for learning and practice, but when you use SQL outside this book, you’ll need to write more flexible queries.

If you want to look at the total sales of a title for a given month, such as March 2021, you can write a query to do that now. But what if you want to run a similar query for April or need total sales for a different title or a different range of dates? Do you have to write a different query for each title and date range?

I assure you that you don’t. All you have to do is learn how to use variables. A variable is a memory-based object that stores a value that, once defined, can be used repeatedly throughout a query or in subsequent queries. More important, the stored value can vary from one execution to another, which means that the value is variable—hence, the name.

Considering the flexibility that variables provide, you’ll use them with great frequency throughout your SQL. Let’s get started!

13.1 User-defined variables

13.1.1 Declaring your first user-defined variable

13.1.2 Understanding rules for user-defined variables

13.1.3 Using your first user-defined variable

13.2 Filtering with variables in FROM and HAVING clauses

13.3 Assigning an unknown value to a variable

13.3.1 Reviewing how a query works

13.3.2 Assigning an unknown variable with SELECT

13.3.3 Considering performance with variables

13.3.4 Troubleshooting considerations with variables

13.4 Other notes about variables

13.4.1 Assigning a literal value using SELECT

13.4.2 Assigning a value of NULL to a variable

13.4.3 Changing the type of data used by a variable

13.5 Lab

13.6 Lab answers