Variables are objects which hold the value in memory for us so we can use that value at different points in our program. TSQL also let us create variables like other programming languages.
Let's create a table and then insert few records for testing purpose, we will be using to test some scenarios how to set the value of variable from a table.
--Create Sample Table with Data
CREATE TABLE [dbo].[VariableTestTable](
[id] [int] NULL,
[first_name] [varchar](50) NULL,
[last_name] [varchar](50) NULL,
[email] [varchar](50) NULL,
[country] [varchar](50) NULL
)
GO
--Insert some Records
SELECT *FROM (SELECT N'1' AS [id],
N'Keith' AS [first_name],
N'Welch' AS [last_name],
N'kwelch0@auda.org.au' AS [email],
N'China' AS [country]
UNION ALL
SELECT N'2' AS [id],
N'Nicholas' AS [first_name],
N'Gomez' AS [last_name],
N'ngomez1@yandex.ru' AS [email],
N'Peru' AS [country]
UNION ALL
SELECT N'3' AS [id],
N'Phillip' AS [first_name],
N'Bell' AS [last_name],
N'pbell2@uol.com.br' AS [email],
N'Brazil' AS [country]) t;
Create Variable:
To Create variable we have to use keyword Declare and then provide the name of variable and finally provide the data type of variable.
Declare @VariableName DataType
Let's create a variable in which we can save First Name value. As we know that the First Name is string , so we will be declare a variable of Varchar ( String). Once we declare the variable , we will set the value by using SET or by using Select.
--Declare Variable
DECLARE @FirstName VARCHAR(50)
--SET the value of variable by using SET
SET @FirstName=(SELECT [first_name] FROM [dbo].[VariableTestTable])
--Print the value of Variable which is assigned by SET Keyword
PRINT @FirstName
--Set the value of variable by using SELECT
SELECT @FirstName=[first_name] FROM [dbo].[VariableTestTable]
--Print the value of variable which is set by SELECT statement
PRINT @FirstName
Let's run above statements and see what we get
Fig 1: Set the value of a Variable in TSQL
As we can see that when we tried to set the value of variable by using SET keyword, it threw an error as the statement we used to set the value of variable was returning more than one value and we can not save those values in VARCHAR type variables.
When we used SELECT to save the value of variable, it kept executing the select and finally set the value of variable to last value it returned by Select statement which was Phillip in this case.
So we need to note two points:
1- If we use SET and our query which is returning us value , will return more than one value, our statement will fail. Make sure we use correct query to return single value.
2-If we use Select then we should make sure , we use filters to return one required value, otherwise it will set the value to last value returned by Select statement.
