How to assign value to a variable in SQL Server Transact-SQL? How to use the SET statements?

In Transact-SQL, you can use SET statements to assign values to variables using the assignment operator =, in the syntax of:

SET @variable = <value>

When an SET statement is executed, the system will:

• Store the value to the variable storage, if the data type of the value matches the variable.
• Or perform an implicit conversion from value's data type to variable's data type, if the value's data type does not match the variable. Then store the value.
• Or return with an error, if the implicit conversion fails.

Here are some example on how to assign values to variables:

-- Data type matches, no implicit conversion
DECLARE @price MONEY;
SET @price = 9.99;
PRINT @price;
----
9.99

-- Data type does not match, implicit conversion applied
DECLARE @now DATETIME; 
SET @now = 'Jan 1, 2000'
PRINT @now;
-------------------
Jan  1 2000 12:00AM

-- Implicit conversion fails
DECLARE @bonus MONEY; 
SET @bonus = 'FIFTY DOLLARS';
PRINT @bonus;
-----------------------------------
Msg 235, Level 16, State 0, Line 13
Cannot convert a char value to money. 
The char value has incorrect syntax.

⇒ PRINT Statements in SQL Server Transact-SQL

⇐ DECLARE Statements in SQL Server Transact-SQL

⇑ Variables and Data Types in SQL Server Transact-SQL

⇑⇑ SQL Server Transact-SQL Tutorials