Assignment Operator

Operators in SQL Server

In this article, I will discuss Assignment Operator in SQL Server

An operator is a symbol which 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. Special operator
  6. Set operator

Use below script to create and populate Employee table with some test 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

Fetch the records

Select * From Employee

assignment operator in sql server

In this article, I will discuss Assignment Operator, rest all other operators will discuss one by one in our upcoming articles.

Assignment Operator:

The assignment operator is used to assign the values to a variable by using “=” operator.

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

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;

SQL SERVER 2008 has introduced a new concept of Compound Assignment Operators.

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.

The following example is without using Compound Assignment Operators.

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

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 will discuss arithmetic operators in SQL Server.

SUMMARY

In this article, I try to explain the Assignment Operator in SQL Server step by step with some examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

1 thought on “Assignment Operator”

Leave a Reply

Your email address will not be published. Required fields are marked *