Assignment Operator in SQL Server

Back to: SQL Server Tutorial For Beginners and Professionals

Assignment Operator in SQL Server with Examples

In this article, I am going to discuss Assignment Operator in SQL Server with Examples. Please read our previous article, where we discussed Clauses in SQL Server. Before understanding Assignment Operator in SQL Server, let us first understand what are operators and why we need operators, and what are the different types of operators available in SQL Server.

What is an Operator in SQL Server?

An operator is a symbol that performs some specific operation on operands or expressions. These operators are classified as follows in SQL Server.

1. Assignment operator
2. Arithmetic operator
3. Comparison operator
4. Logical operator
5. Set operator

Note: In this article, I am going to discuss Assignment Operator, rest of all other operators will discuss one by one from our upcoming articles.

Understanding the Assignment Operator in SQL Server:

Let us understand how to use the Assignment Operator in SQL Server with an example. We are going to use the following Employee table to understand the Assignment Operator.

Please use the below script to create and populate the Employee table with the required data.

--Create database EmployeeDB
Create database EmployeeDB
Go

Use EmployeeDB
Go

--Create Employee table
CREATE TABLE Employee
(
  ID INT PRIMARY KEY IDENTITY(1,1),
  Name VARCHAR(100),
  EmailID VARCHAR(100),
  Gender VARCHAR(100),
  Department VARCHAR(100),
  Salary INT,
  Age INT,
  CITY VARCHAR(100)
)
GO

--Insert some data into Employee table
INSERT INTO Employee VALUES('Pranaya','Pranaya@g.com','Male', 'IT', 25000, 30,'Mumbai')
INSERT INTO Employee VALUES('Tarun','Tarun@g.com','Male', 'Payroll', 30000, 27,'Odisha')
INSERT INTO Employee VALUES('Priyanka','Priyanka@g.com','Female', 'IT', 27000, 25,'Bangalore')
INSERT INTO Employee VALUES('Preety','Preety@g.com','Female', 'HR', 35000, 26,'Bangalore')
INSERT INTO Employee VALUES('Ramesh','Ramesh@g.com','Male','IT', 26000, 27,'Mumbai')
INSERT INTO Employee VALUES('Pramod','Pramod@g.com','Male','HR', 29000, 28,'Odisha')
INSERT INTO Employee VALUES('Anurag','Anurag@g.com','Male', 'Payroll', 27000, 26,'Odisha')
INSERT INTO Employee VALUES('Hina','Hina@g.com','Female','HR', 26000, 30,'Mumbai')
INSERT INTO Employee VALUES('Sambit','Sambit@g.com','Male','Payroll', 30000, 25,'Odisha')
INSERT INTO Employee VALUES('Manoj','Manoj@g.com','Male','HR', 30000, 28,'Odisha')
INSERT INTO Employee VALUES('Sara',' Sara@g.com','Female', 'Payroll', 28000, 27,'Mumbai')
INSERT INTO Employee VALUES('Lima','Lima@g.com','Female','HR', 30000, 30,'Bangalore')
INSERT INTO Employee VALUES('Dipak','Dipak@g.com','Male','Payroll', 32000, 25,'Bangalore')
GO

Assignment Operator:

The assignment operator (=) in SQL Server is used to assign the values to a variable. The equal sign (=) is the only Transact-SQL assignment operator. In the following example, we create the @MyCounter variable, and then the assignment operator sets the @MyCounter variable to a value i.e. 1.

DECLARE @MyCounter INT;
SET @MyCounter = 1;

The assignment operator can also be used to establish the relationship between a column heading and the expression that defines the values for that column. The following example displays the column headings as FirstColumn and SecondColumn. The string ‘abcd‘ is displayed for all the rows in the FirstColumn column heading. Then, each Employee ID from the Employee table is listed in the SecondColumn column heading.

SELECT FirstColumn = ‘abcd’, SecondColumn = ID FROM Employee;

Compound Assignment Operators in SQL Server:

SQL SERVER 2008 has introduced a new concept of Compound Assignment Operators. The Compound Assignment Operators are available in many other programming languages for quite some time. Compound Assignment Operators are operated where variables are operated upon and assigned in the same line. Compound-assignment operators provide a shorter syntax for assigning the result of an arithmetic or bitwise operator. They perform the operation on the two operands before assigning the result to the first operand.

Example without using Compound Assignment Operators

The following example is without using Compound Assignment Operators.

DECLARE @MyVariable INT
SET @MyVariable = 10
SET @MyVariable = @MyVariable * 5
SELECT @MyVariable AS MyResult
GO

Example using Compound Assignment Operators

The above example can be rewritten using Compound Assignment Operators as follows.

DECLARE @MyVariable INT
SET @MyVariable = 10
SET @MyVariable *= 5
SELECT @MyVariable AS MyResult
GO

Following are the list of available compound operators in SQL Server

+= Adds some amount to the original value and sets the original value to the result.
-= Subtracts some amount from the original value and sets the original value to the result.
*= Multiplies by an amount and sets the original value to the result.
/= Divides by an amount and sets the original value to the result.
%= Divides by an amount and sets the original value to the modulo.

In the next article, I am going to discuss Arithmetic Operators in SQL Server. Here, in this article, I try to explain the Assignment Operator in SQL Server with Examples. I hope this article will help you with your needs. I would like to have your feedback. Please post your feedback, question, or comments about this article.