In this post we are going to cover how to declare, set the value and use a variable in a Microsoft SQL.
What is a variable?
Before we get into how to use them, it is best if we define what a variable is and why we might want to use them.
A variable is a named storage location that can hold a value, which can be of diverse types (e.g., integer, float, string, etc.). The value stored in a variable can be changed during the execution of a query.
Variables are used to store data and that data can be set and read by referring to the variable name. So, you can set a value once in your query, and use the variable name in multiple places, meaning if required I only need to change the value in one place.
The concept in SQL is the same as most programming languages.
How to declare a variable in SQL?
Before we can use a variable you first need to declare (create) it. In Microsoft SQL, you can declare a variable by using the DECLARE keyword, followed by the variable name and its data type. For example:
DECLARE @variableName INT
This will declare a variable named “variableName” with the data type INT (integer).
How to set the value of variable in SQL?
To assign a value to a variable, you can use the SET keyword, followed by the variable name and the value you want to assign. For example:
SET @variableName = 5
This will assign the value of 5 to the “variableName” variable.
How to use a variable in a SQL query?
You can then use the variable in a query, trigger, or stored procedure to insert a value into the query for example.
SELECT * FROM myTable WHERE myTable.id = @variableName
Or can also use a variable to store the result of a query for later use, for example
DECLARE @variableName INT
SET @variableName = (Select Count(*) FROM myTable)
Select @variableName
The above would declare the variable ‘variableName’ for use as an integer data time and store the number of records returned by the query between the parenthesis ().
The final line is just example that you can use a variable as main of the select, in this case you would get a column with the number of rows returned.