How to set value of a Variable in TSQL

How to set value of a Variable in TSQL

Variables are objects that store a value in our program's memory for us to use at certain stages. Like other programming languages, TSQL also allowed for the creation of variables.

For testing purposes, let's create a table and then add a few records. We'll use this table to test various situations for setting variable values 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

 

As we can see, the SET keyword failed to set the value of the variable because the statement we used to set the value of the variable returned several values and we are unable to store those values in VARCHAR type variables.

 When we used SELECT to save the value of a variable, it continued to execute the select statement until it ultimately set the value of the variable to the last value it returned, which in this case was Phillip.

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.