Stored Procedure Programming Concepts - Working with Variables | Essential SQL

What is a variable? A variable can hold a value of a specific type. A type is a date or an integer, like a whole number or a decimal. 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.
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 data types, I do have information on that at my website. I have a reference to that in this lesson.
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.