I recently put together a lesson on how to work with TSQL variables. When you create stored procedures, you can encapsulate logic securely, and part of this process is using TSQL variables to temporarily store and manipulate values.
After watching this video you’ll be able to declare TSQL variables and understand how they are used in expressions. You’ll find out isn’t as hard as everyone makes them out to be and you’ll have one of the skills a junior DBA should know.
You can learn more about this this video, it’s transcript follows:
Defining TSQL Variables
Now it’s time to start working with tsql variables.
What is a tsql variable? A tsql variable can hold a value of a specific type. A type is a date or an integer, like a whole number or a decimal. TSQL variables can be used to work in mathematical expressions like adding values together or to hold text and combine text.
They can be used as part of the equations. Variables are used to store information to participate in calculations. Variables are also part of our SQL and used in stored procedures to be part of our parameters.
Declaring a Variable
In order to use a TSQL variable we need to declare it. We use the declare command. Here we have a couple examples. We put an @ sign in front of the variable. Here I have “declare@countint” and then “;”. So I’m saying declare the variable count as an integer. I can also say, declare city as a varchar 40 and then give it an initial value of Ann Arbor.
This is a handy way of allowing you to not only declare the variable, but also set it up with a value right off the bat. Varchar again is a data type that allows you to have multiple characters in one string.
Again, I’m assuming that you understand what data types are. They’re part of an introductory SQL class. If you don’t understand datatypes, I do have information on that at my website. I have a reference to thats in this lesson.
Assigning Value to a Variable
To assign a value to a variable, you could do that in the DECLARE statement or you can also use the set command. If I had a variable called height and I wanted to set it to a value of 10.5, I could say set height equal 10.5. Or I could even use an expression like I want to set weight to a base weight and then multiply it by a value of 1.05. Here I have an instance where I’m setting my height to a literal value. In another case I am setting my variable to an expression.
There’s a couple of ways I can use variables in SQL. One way we’ve seen is just to create a simple expression where I’m multiplying some values. I can use also variables within an SQL select statement. But here I have a variable called PersonID and it’s participating in the SQL statement as part of the query. In fact, it’s part of the criteria where it’s going to be the business entity ID. So we’re gonna say we are the business entity ID at the person table is going to equal the contents of this person ID variable.
That becomes very powerful, especially when we’re working with stored procedures and parameters and we’re bringing in values and those values are in the variable. Then we can make our SQL a little more dynamic.
Let’s go look at some quick examples of how we use variables to further show you how this is done.
Leave a Reply