SQL Server - How to Declare Variables and Set Values in Variable

watch_later Thursday, February 21, 2019

Introduction


This article gives an explanation about how to declare variable and set value in SQL server and how to set value in a variable in SQL server as well as set value in the variable in SQL server and how to set variable value in SQL Server stored procedure and how to set a default value for the variable in SQL server and how to set variable value in select statement in SQL server and also shows how to set multiple values to a variable in SQL server and declare variable and set value in SQL query.


SQL Server - How to Declare Variables and Set Values in Variable

Many developers/students those who work with Microsoft SQL Server will have at least heard talk about the variables in SQL Server. Even if any developers/students know on a basic level what is variables in SQL Server, use of variables in SQL Server as well as set values in the local variable and use that value as per need, but still many developers/students didn't know about variables in SQL Server, they didn't know how to use variables, how to set values in variable even they don't know how to declare variables in SQL Server, So in this article I am going to share some basic information regarding variables in SQL.

Requirement


1) Give Simple Definition of the variable in SQL Server
2) Explain the Syntex of Variable Declaration in SQL Server
3) GIve some Example to Store values in the variable.
4) GIve some Example to Store multiple values in the variable.

What is a variable in SQL Server?


In SQL Server, a variable allows a programmer/developers/students to store data temporarily during the execution of code.

Syntex to Declare Variable in SQL Server

DECLARE @Your_variable_name datatype [ = initial_value ],
        @Your_variable_name datatype [ = initial_value ],
 
        ...;
If you analyzed above syntax then @Your_variable_name indicates the name of your variable. and datatype indicates datatype of your variables such as VARCHAR(50), INT, FLOAT, and etc.and [ = initial_value ] indicate default value for your variable.

Example


Let's take a simple example for variable declaration in SQL server.

1) Declare a Single variable with Default Value

DECLARE @EmployeeName VARCHAR(50) = 'Nikunj Satasiya'
Here, I have declared variable EmployeeName with datatype VARCHAR(50) and default value 'Nikunj Satasiya'. Now, If I will Print or Select declared variable  @EmployeeName then you can see result same as shown below.
PRINT @EmployeeName
--SELECT @EmployeeName AS Employee

Result

Nikunj Satasiya
 
    (1 row(s) affected)

2) Declare a Single variable Without Default Value

DECLARE @EmployeeName VARCHAR(50)
Here, I have declared the same variable as per the case 1 @EmployeeName with datatype VARCHAR(50) but I didn't pass any default value. Now, I'll show you how you can set values in the declared variable and get expected result set as output.
-- Variable Declaration 
DECLARE @EmployeeName VARCHAR(50)
-- Set Value in Variable 
SET @EmployeeName =  'Nikunj Satasiya'
-- Print Variable OR SELECT Variable
PRINT @EmployeeName
 
SELECT @EmployeeName AS Employee

Result

Nikunj Satasiya
 
    (1 row(s) affected)
If, You analyzed the second statement then here I have use SET to assign values in variable EmployeeName. In SQL server SET is used to assign a value to a variable.

3) Declare Multiple variables With Default Value

-- Multiple Variable Declaration With Default Value
DECLARE @EmployeeName VARCHAR(50) =  'Nikunj Satasiya', @Company VARCHAR(50)=  'Casepoint LLC.'
 
-- Print OR SELECT Multiple Variables
PRINT @EmployeeName
 
PRINT @Company
 
SELECT @EmployeeName AS Employee
 
SELECT @Company AS Company

Result

Nikunj Satasiya
Casepoint LLC.
 
    (1 row(s) affected)
    (1 row(s) affected)
Here, I declared variable same as case one but here I declared multiple variables with the same Datatype and different default value.

4) Set Values in variables with Select Statment 


Here, I'll show you how you can set the value of the column using select statement into a variable.

Method 1: Set value in Single Variable.

--Set Value in Single Variable With SELECT Query in SQL SEREVR
SELECT @EmployeeName = EmployeeName FROM EmployeeName_Master WHERE EmpID  = 104
PRINT @EmployeeName

Result

Nikunj Satasiya
 
    (1 row(s) affected)

Method 2: Set value in Multiple Variable.

--Set Value in Multiple Variable With SELECT Query in SQL SEREVR
SELECT @EmployeeName = EmployeeName, @Company = Company FROM EmployeeName_Master WHERE EmpID  = 104
PRINT @EmployeeName
 
PRINT @Company
Result
Nikunj Satasiya
Casepoint LLC.
 
    (1 row(s) affected)
    (1 row(s) affected)
If you analyzed method one and method two then @EmployeeName and @Compnay is variable and values of column EmployeeName is stored in EmployeeName variable and values of column Company is stored in the @Compnay variable whose EmpID is 104.

Summary


This article gives an explanation about how to declare the variable and set value in SQL server as well as shows a different kind for methods to stores values in a variable and it's used in SQL server.

If you have any questions/query regarding this then please you can ask your queries I will help you to resolve your queries and issues regarding your SQL server database.

Codingvila provides articles and blogs on web and software development for beginners as well as free Academic projects for final year students in Asp.Net, MVC, C#, Vb.Net, SQL Server, Angular Js, Android, PHP, Java, Python, Desktop Software Application and etc.



sentiment_satisfied Emoticon