What are the different ways to set value of a Variable in TSQL

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.


1 comment:

  1. Enthusiastic beginning is very common in every sector when anyone enters into a new world. But it is very hard to keep such enthusiasm for a long time after huddles come out from invited sources. Read this blog and know more about this topic. Los Angeles vps

    ReplyDelete