How to Declare Variables and Assign Values in SQL Server

Add Comment

A variable in SQL Server lets you store a value once and reuse it in later statements. This article explains how to declare a variable and set a value in SQL Server. It covers declaring single and multiple variables, giving a variable an initial value, setting a value with the SET and SELECT statements, storing multiple values in a variable, and using variables in a stored procedure.

The basic examples work on SQL Server 2008 and later, as well as on Azure SQL Database and Azure SQL Managed Instance. Features that need a newer version are marked where they appear.

In my previous articles, I explained Temp Table vs Table Variable vs CTE in SQL Server and SQL Server PIVOT and UNPIVOT: Dynamic SQL Case Study, which pair well with this article.

How to Declare Variables and Assign Values in SQL Server

Many developers and students who work with Microsoft SQL Server have heard about variables, but they are often unsure how to declare them, how to assign values, or what happens when a query returns no rows or many rows.

This article covers those basics and the behaviors that many tutorials skip.

What You Will Learn

  1. A simple definition of a variable in SQL Server.
  2. The syntax to declare a variable, and what an initial value really is.
  3. How to store a value in a variable with SET and with SELECT, and how the two differ.
  4. How to declare multiple variables.
  5. How to store multiple values in a single variable.
  6. How to use variables in a stored procedure.
  7. Variable scope, common mistakes and best practices.

What is a Variable in SQL Server?

In SQL Server, a variable (more precisely, a local variable) stores a single value of a specific data type temporarily while code is running. Its name always starts with one @ symbol, and it exists only until the batch, stored procedure, function or trigger in which it was declared finishes.

Do not confuse variables with names that start with @@, such as @@ROWCOUNT, @@ERROR and @@VERSION. Those are system functions provided by SQL Server, and you cannot declare your own.

A variable holds one value. If you need to hold many rows, use a table variable or a temporary table.

Syntax to Declare a Variable in SQL Server

sql
DECLARE @variable_name data_type [ = initial_value ],
        @variable_name data_type [ = initial_value ],
        ...;

In this syntax, @variable_name is the name of your variable, and data_type is its data type, such as VARCHAR(50), INT, DECIMAL(10, 2) or DATE. A variable cannot use the text, ntext or image data types. The optional [ = initial_value ] part assigns a value at the moment of declaration.

A note on terminology: SQL Server has no "default value" for local variables. What you assign in the DECLARE statement is an initial value. It can be a constant or an expression, such as GETDATE(), as long as it matches or can be implicitly converted to the data type.

Assigning a value in the DECLARE statement requires SQL Server 2008 or later. If you leave it out, the variable is NULL until you assign something. Real default values exist only for the parameters of stored procedures and functions, which we will see later.

Microsoft recommends ending T-SQL statements with a semicolon, so the examples in this article do so.

Examples of Declaring Variables

Let's take some simple examples of variable declaration in SQL Server.

1) Declare a Single Variable with an Initial Value

sql
DECLARE @EmployeeName VARCHAR(50) = 'Nikunj Satasiya';

PRINT @EmployeeName;

SELECT @EmployeeName AS Employee;

Here, I have declared the variable @EmployeeName with the data type VARCHAR(50) and the initial value 'Nikunj Satasiya'. PRINT sends the value to the Messages tab, while SELECT returns it as a result set in the Results tab.

Result

text
Messages tab:
Nikunj Satasiya

(1 row affected)

Results tab:
Employee
---------------
Nikunj Satasiya

2) Declare a Single Variable Without an Initial Value

sql
DECLARE @EmployeeName VARCHAR(50);

SELECT @EmployeeName AS BeforeAssignment;

SET @EmployeeName = 'Nikunj Satasiya';

SELECT @EmployeeName AS AfterAssignment;

Here, I have declared the same variable as in case 1, but without an initial value. The first SELECT shows that the variable is NULL before any assignment. In SQL Server, every declared local variable starts as NULL unless you give it an initial value.

Then I use SET to assign a value, and the second SELECT shows it. SET is the most common way to assign a value to a variable in SQL Server, and in the next sections you will see that SELECT can do it too.

Result (two result sets in the Results tab)

text
BeforeAssignment
----------------
NULL

AfterAssignment
---------------
Nikunj Satasiya

3) Declare Multiple Variables with Initial Values

sql
DECLARE @EmployeeName VARCHAR(50) = 'Nikunj Satasiya',
        @Company VARCHAR(50) = 'Infosys Limited.';

PRINT @EmployeeName;
PRINT @Company;

SELECT @EmployeeName AS Employee, @Company AS Company;

Here, I declared multiple variables in a single DECLARE statement, separated by commas. Each variable has its own data type and its own initial value. A variable name must be unique within a batch, otherwise SQL Server raises an error saying the variable has already been declared.

Result

text
Messages tab:
Nikunj Satasiya
Infosys Limited.

(1 row affected)

Results tab:
Employee         Company
---------------  --------------
Nikunj Satasiya  Infosys Limited.

4) Declare Variables with Different Data Types

sql
DECLARE @EmpID INT = 104,
        @Salary DECIMAL(10, 2) = 55000.50,
        @JoinDate DATE = '2026-09-24',
        @IsActive BIT = 1;

SET @Salary += 2500;

SELECT @EmpID AS EmpID, @Salary AS Salary, @JoinDate AS JoinDate, @IsActive AS IsActive;

Use the data type that matches the value you want to store. The statement SET @Salary += 2500 uses a compound assignment operator, which adds 2500 to the current value. The operators +=, -=, *= and /= are available from SQL Server 2008.

For a DATE or DATETIME2 variable, the yyyy-mm-dd format is interpreted the same way regardless of the session's language and date format settings. This is not true for the older DATETIME and SMALLDATETIME types, where yyyy-mm-dd can be misread under some language settings. For those types, use the unseparated yyyyMMdd format, which is safe for every date and time type.

Always give VARCHAR, NVARCHAR and DECIMAL an explicit length or precision, for the reason explained in the "Common Mistakes" section below. Use NVARCHAR instead of VARCHAR if the value can contain Unicode characters.

Result

text
EmpID  Salary    JoinDate    IsActive
-----  --------  ----------  --------
104    55000.50  2026-09-24  1

Sample Data Used in the Next Examples

The next examples read data from a table, so run this script first to create and fill it.

sql
CREATE TABLE dbo.EmployeeName_Master
(
    EmpID        INT         NOT NULL PRIMARY KEY,
    EmployeeName VARCHAR(50) NOT NULL,
    Company      VARCHAR(50) NOT NULL
);

INSERT INTO dbo.EmployeeName_Master (EmpID, EmployeeName, Company)
VALUES (101, 'Mansi Satasiya',  'Infosys Limited.'),
       (102, 'Hiren Dobariya',  'Infosys Limited.'),
       (104, 'Nikunj Satasiya', 'Infosys Limited.');

5) Set Values in Variables with a SELECT Statement

Here, I'll show you how to store the value of a column in a variable using a SELECT statement.

Method 1: Set a Value in a Single Variable

sql
DECLARE @EmployeeName VARCHAR(50);

SELECT @EmployeeName = EmployeeName
FROM dbo.EmployeeName_Master
WHERE EmpID = 104;

PRINT @EmployeeName;

Result (Messages tab)

text
(1 row affected)
Nikunj Satasiya

Method 2: Set Values in Multiple Variables

sql
DECLARE @EmployeeName VARCHAR(50),
        @Company VARCHAR(50);

SELECT @EmployeeName = EmployeeName,
       @Company = Company
FROM dbo.EmployeeName_Master
WHERE EmpID = 104;

PRINT @EmployeeName;
PRINT @Company;

Result (Messages tab)

text
(1 row affected)
Nikunj Satasiya
Infosys Limited.

In both methods, @EmployeeName and @Company are variables. The value of the column EmployeeName is stored in @EmployeeName, and the value of the column Company is stored in @Company, for the row whose EmpID is 104.

Do Not Mix Assignment with Data Retrieval

A SELECT statement that assigns values to variables cannot also return a result set. If you mix an assignment and a normal column in the same select list, SQL Server refuses to run the batch:

sql
DECLARE @EmployeeName VARCHAR(50);

SELECT @EmployeeName = EmployeeName, Company
FROM dbo.EmployeeName_Master
WHERE EmpID = 104;

Result

text
Msg 141, Level 15, State 1
A SELECT statement that assigns a value to a variable must not be combined with
data-retrieval operations.

To fix it, assign every column in the select list to a variable, or use two separate statements: one to assign the variable and another to return the data.

6) SET vs SELECT: What is the Difference?

Both statements can assign a value to a variable, but they behave differently in the situations below. Microsoft's documentation recommends SET for assigning variable values, and knowing these differences shows why.

  • Assigning several variables in one statement: SELECT can do it, while SET assigns one variable per statement.
  • The query returns no rows: with SET @var = (SELECT ...), the variable becomes NULL. With SELECT @var = column FROM ..., the variable keeps its current value.
  • The query returns more than one row: SET with a subquery raises an error. SELECT raises no error and stores the last value returned.
  • Effect on @@ROWCOUNT: after SELECT @var = ... it holds the number of rows the query matched, while after SET @var = (SELECT ...) it is 1 even when the subquery found nothing.

When No Rows Are Returned

sql
DECLARE @EmployeeName VARCHAR(50) = 'Not found';

SELECT @EmployeeName = EmployeeName
FROM dbo.EmployeeName_Master
WHERE EmpID = 999;

SELECT @EmployeeName AS AfterSelect;

SET @EmployeeName = (SELECT EmployeeName FROM dbo.EmployeeName_Master WHERE EmpID = 999);

SELECT @EmployeeName AS AfterSet;

No row has EmpID 999. The first assignment (with SELECT) leaves the variable unchanged, while the second (with SET and a subquery) sets it to NULL.

Result

text
AfterSelect
-----------
Not found

AfterSet
--------
NULL

To detect that no row matched, check @@ROWCOUNT immediately after the SELECT assignment:

sql
DECLARE @EmployeeName VARCHAR(50);

SELECT @EmployeeName = EmployeeName
FROM dbo.EmployeeName_Master
WHERE EmpID = 999;

IF @@ROWCOUNT = 0
    PRINT 'No employee found.';

Keep in mind that @@ROWCOUNT is overwritten by the next statement that sets it, including another SELECT or a SET assignment, so read it right away. If you need to run other statements before making a decision, copy it into a variable straight after the query:

sql
DECLARE @EmployeeName VARCHAR(50),
        @RowsFound INT;

SELECT @EmployeeName = EmployeeName
FROM dbo.EmployeeName_Master
WHERE EmpID = 999;

SET @RowsFound = @@ROWCOUNT;

IF @RowsFound = 0
    PRINT 'No employee found.';

This check does not work after a SET assignment. In practice, SET @var = (SELECT ...) reports @@ROWCOUNT as 1 even when the subquery returns no row, so an IF @@ROWCOUNT = 0 test after it never fires:

sql
DECLARE @EmployeeName VARCHAR(50);

SET @EmployeeName = (SELECT EmployeeName FROM dbo.EmployeeName_Master WHERE EmpID = 999);

SELECT @@ROWCOUNT AS RowCountAfterSet;

Result

text
RowCountAfterSet
----------------
1

If you use SET with a subquery, test the variable for NULL (when the column cannot be NULL) or use IF EXISTS (...) instead of relying on @@ROWCOUNT.

When More Than One Row Is Returned

sql
DECLARE @EmployeeName VARCHAR(50);

SELECT @EmployeeName = EmployeeName
FROM dbo.EmployeeName_Master
WHERE Company = 'Infosys Limited.'
ORDER BY EmpID;

SELECT @EmployeeName AS Employee;

Three rows match, and there is no error. The variable receives the last value returned. Without an ORDER BY, which row comes last is not guaranteed.

Result

text
Messages tab:
(3 rows affected)

Results tab:
Employee
---------------
Nikunj Satasiya

Even with an ORDER BY, relying on "the last row wins" makes the intent hard to see when someone reads the code. Prefer a query that returns exactly one row, for example by filtering on a primary key, or state the choice explicitly with TOP (1) and ORDER BY:

sql
DECLARE @EmployeeName VARCHAR(50);

SELECT TOP (1) @EmployeeName = EmployeeName
FROM dbo.EmployeeName_Master
WHERE Company = 'Infosys Limited.'
ORDER BY EmpID DESC;

This assigns the employee with the highest EmpID, so @EmployeeName holds 'Nikunj Satasiya'.

The same multi-row query written with SET and a subquery fails:

sql
DECLARE @EmployeeName VARCHAR(50);

SET @EmployeeName = (SELECT EmployeeName FROM dbo.EmployeeName_Master WHERE Company = 'Infosys Limited.');

Result

text
Msg 512, Level 16
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

7) Store Multiple Values in a Single Variable

A variable holds one value, but you can combine many values into one string, or keep many rows in a table variable.

Method 1: Combine Values into One String with STRING_AGG (SQL Server 2017 and Later)

sql
DECLARE @EmployeeList NVARCHAR(MAX);

SELECT @EmployeeList = STRING_AGG(CAST(EmployeeName AS NVARCHAR(MAX)), ', ')
                            WITHIN GROUP (ORDER BY EmpID)
FROM dbo.EmployeeName_Master;

SELECT @EmployeeList AS Employees;

Result

text
Employees
-----------------------------------------------
Mansi Satasiya, Hiren Dobariya, Nikunj Satasiya

STRING_AGG requires SQL Server 2017 or later (or Azure SQL). The function is available at any database compatibility level, but the WITHIN GROUP (ORDER BY ...) clause needs compatibility level 110 or higher. NULL values are skipped.

When the input is not a MAX type, the result type is limited to NVARCHAR(4000) or VARCHAR(8000), so I cast the column to NVARCHAR(MAX) to be safe with larger lists.

Method 2: FOR XML PATH with STUFF (Older Versions)

sql
DECLARE @EmployeeList NVARCHAR(MAX);

SELECT @EmployeeList = STUFF((SELECT ', ' + EmployeeName
                              FROM dbo.EmployeeName_Master
                              ORDER BY EmpID
                              FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '');

SELECT @EmployeeList AS Employees;

This returns the same list and works on older versions. STUFF() removes the leading comma and space, and the TYPE directive with .value() keeps special characters such as & from being XML-encoded. You can read more in my article SQL Server STUFF() Function With Syntax and Example.

I do not recommend the older pattern SELECT @v = @v + column FROM ..., because Microsoft does not guarantee it works reliably, especially with ORDER BY.

Method 3: Keep Many Rows in a Table Variable

sql
DECLARE @Employees TABLE
(
    EmpID        INT,
    EmployeeName VARCHAR(50)
);

INSERT INTO @Employees (EmpID, EmployeeName)
SELECT EmpID, EmployeeName
FROM dbo.EmployeeName_Master
WHERE Company = 'Infosys Limited.';

SELECT EmpID, EmployeeName
FROM @Employees;

A table variable is declared with DECLARE @name TABLE (...) and has the same batch scope as any other variable.

Table variables do not have distribution statistics, so the optimizer's row estimates can be poor when they hold many rows. Before SQL Server 2019, the optimizer typically assumed a very small row count for a table variable, whatever its real size. SQL Server 2019 and later, at compatibility level 150, improves this with deferred compilation, which uses the actual row count, although column statistics are still missing.

As a rule of thumb, table variables suit small sets of rows, and there is no fixed row count at which they stop working well, so test with your own data. For larger sets, or when the data is joined to big tables, a temporary table such as #TempTable is often a better choice, because it has statistics and you can create indexes on it after it is created.

Result

text
EmpID  EmployeeName
-----  ---------------
101    Mansi Satasiya
102    Hiren Dobariya
104    Nikunj Satasiya

8) Set Variable Values in a SQL Server Stored Procedure

Inside a stored procedure, you declare and set local variables exactly as shown above. The procedure's parameters also behave like variables, and, unlike local variables, they can have real default values (for example @EmpID INT = 104).

sql
CREATE OR ALTER PROCEDURE dbo.usp_GetEmployeeDetails
    @EmpID INT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @EmployeeName VARCHAR(50),
            @Company VARCHAR(50);

    SELECT @EmployeeName = EmployeeName,
           @Company = Company
    FROM dbo.EmployeeName_Master
    WHERE EmpID = @EmpID;

    SELECT @EmployeeName AS Employee, @Company AS Company;
END

CREATE OR ALTER requires SQL Server 2016 SP1 or later. On older versions, use CREATE PROCEDURE and ALTER PROCEDURE separately.

Run the CREATE statement in its own batch, because CREATE PROCEDURE must be the first statement in a batch.

SET NOCOUNT ON stops SQL Server from sending "rows affected" messages, which reduces network traffic.

Now execute the procedure:

sql
EXEC dbo.usp_GetEmployeeDetails @EmpID = 104;

Result

text
Employee         Company
---------------  --------------
Nikunj Satasiya  Infosys Limited.

To send a value back to the caller, use an OUTPUT parameter:

sql
CREATE OR ALTER PROCEDURE dbo.usp_GetEmployeeName
    @EmpID INT,
    @EmployeeName VARCHAR(50) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT @EmployeeName = EmployeeName
    FROM dbo.EmployeeName_Master
    WHERE EmpID = @EmpID;
END

The caller declares a variable and passes it with the OUTPUT keyword:

sql
DECLARE @Name VARCHAR(50);

EXEC dbo.usp_GetEmployeeName @EmpID = 104, @EmployeeName = @Name OUTPUT;

SELECT @Name AS Employee;

Result

text
Employee
---------------
Nikunj Satasiya

Variable Scope and Common Mistakes

1) A Variable Exists Only in Its Batch

sql
DECLARE @EmployeeName VARCHAR(50) = 'Nikunj Satasiya';
PRINT @EmployeeName;
GO

PRINT @EmployeeName;

The first PRINT works. The second fails, because GO ends the batch and the variable no longer exists. GO is not a T-SQL statement but a batch separator understood by client tools such as SSMS and sqlcmd.

Result

text
Nikunj Satasiya
Msg 137: Must declare the scalar variable "@EmployeeName".

T-SQL also has no block scope. A variable declared inside an IF ... BEGIN ... END block stays visible until the end of the batch.

2) Missing Length or Precision on VARCHAR and DECIMAL

sql
DECLARE @Code VARCHAR = 'ABC',
        @CodeFixed VARCHAR(10) = 'ABC';

SELECT @Code AS WithoutLength, @CodeFixed AS WithLength;

Result

text
WithoutLength  WithLength
-------------  ----------
A              ABC

When you declare VARCHAR without a length in a variable declaration, SQL Server uses a length of 1, and the assignment silently cuts the value without an error. Assigning a value that is too long for a variable also truncates it silently. Always specify the length.

The same kind of mistake applies to DECIMAL. Without a precision and scale it becomes DECIMAL(18,0), so the fractional part is lost and the value is rounded to a whole number. Declare it as DECIMAL(10, 2) or whatever your data needs.

3) Concatenating with NULL Returns NULL

sql
DECLARE @Name VARCHAR(50);

SELECT 'Hello ' + @Name AS Greeting,
       CONCAT('Hello ', @Name) AS ConcatGreeting,
       'Hello ' + ISNULL(@Name, 'guest') AS DefaultGreeting;

Result

text
Greeting  ConcatGreeting  DefaultGreeting
--------  --------------  ---------------
NULL      Hello           Hello guest

With the default session settings, joining a string and a NULL with + gives NULL. This behavior is controlled by the session setting SET CONCAT_NULL_YIELDS_NULL, whose default is ON. A legacy application may run with it set to OFF, which changes the result.

To make your code independent of that setting, use CONCAT() (SQL Server 2012 and later), which treats NULL as an empty string, or handle the value explicitly with ISNULL().

4) Mismatched Data Types Can Hurt Performance

sql
DECLARE @SearchName NVARCHAR(50) = N'Nikunj Satasiya';

SELECT EmpID
FROM dbo.EmployeeName_Master
WHERE EmployeeName = @SearchName;

In our sample table, EmployeeName is VARCHAR(50), but the variable is NVARCHAR(50). When two different types are compared, SQL Server converts the one with the lower data type precedence to the one with the higher precedence. NVARCHAR ranks higher than VARCHAR, so the column values are converted, not the variable.

If EmployeeName is indexed, this implicit conversion can prevent an efficient index seek and lead to an index scan, depending on the column's collation and the plan chosen. The execution plan shows a CONVERT_IMPLICIT warning in such cases. In the opposite case, a VARCHAR variable compared with an NVARCHAR column, the variable is converted and the index can still be used. The simplest rule is to declare each variable with the same data type and length as the column it is compared with.

5) Variable Names and Letter Case

On most SQL Server installations the default collation is case-insensitive, so @EmployeeName and @employeename refer to the same variable. Variable names, however, are resolved using the collation of the SQL Server instance, not the collation of your database.

If your script later runs on an instance with a case-sensitive or binary collation, such as SQL_Latin1_General_CP1_CS_AS, inconsistent casing fails:

sql
DECLARE @EmployeeName VARCHAR(50) = 'Nikunj';

SELECT @employeename;

Result (on a case-sensitive instance)

text
Msg 137: Must declare the scalar variable "@employeename".

To keep your scripts portable, always write a variable name with exactly the same casing everywhere you use it.

Best Practices

  • Use meaningful names such as @EmployeeName rather than @a, and keep the letter case consistent.
  • Match each variable's data type and length to the column it will be compared with or stored in, to avoid the implicit conversions described above.
  • Always specify the length for VARCHAR, NVARCHAR and CHAR, and the precision and scale for DECIMAL.
  • Prefer SET to assign a single value, as Microsoft recommends. Use SELECT when you need to assign several variables from one row. When assigning from a table, make sure the query returns exactly one row, and if a missing row matters, check @@ROWCOUNT right after a SELECT assignment.
  • Handle NULL explicitly with ISNULL(), COALESCE() or IS NULL checks.
  • SQL Server compiles a batch before the statements that assign values to variables have run, so the optimizer does not know a local variable's value at compile time. It typically falls back on generic estimates based on average statistics instead of the value's histogram.
  • Stored procedure parameters are different: SQL Server "sniffs" their values when it first compiles the procedure. On large tables, check the execution plan. Adding OPTION (RECOMPILE) to the statement can let the optimizer use the variable's current value, at the cost of a recompile each time. See my article SQL Server Basic Performance Tuning Tips and Tricks for more.
  • Variables cannot stand in for object names such as tables or columns. For dynamic SQL, pass values as parameters with sp_executesql instead of concatenating them into the query string, which also protects you against SQL injection. If an object name must be dynamic, validate it and wrap it with QUOTENAME().
  • A variable disappears when its batch ends. If a value must survive across batches, consider a temporary table or SESSION_CONTEXT (SQL Server 2016 and later). It stores named key-value pairs that you set with sp_set_session_context and read with SESSION_CONTEXT(), and the values last for the whole session. It is easier to use than the older CONTEXT_INFO, which holds only a single 128-byte binary value.

Summary

This article explained how to declare a variable and set a value in SQL Server. It covered declaring single and multiple variables, initial values versus NULL, setting values with SET and SELECT, the differences between the two (including no rows, many rows and @@ROWCOUNT), storing multiple values with STRING_AGG, FOR XML PATH and table variables, using variables in stored procedures, and the mistakes to avoid.

If you have any questions/queries regarding this, please ask in the comments. I will help you to resolve your queries and issues regarding your SQL Server database.

References

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.

0 Comments:

If you have any questions, contact us on info.codingvila@gmail.com