sql server variable assignment

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

Variables (Transact-SQL)

• 14 contributors

A Transact-SQL local variable is an object that can hold a single data value of a specific type. Variables in batches and scripts are typically used:

• As a counter either to count the number of times a loop is performed or to control how many times the loop is performed.
• To hold a data value to be tested by a control-of-flow statement.
• To save a data value to be returned by a stored procedure return code or function return value.
• The names of some Transact-SQL system functions begin with two at signs (@@). Although in earlier versions of SQL Server, the @@functions are referred to as global variables, @@functions aren't variables, and they don't have the same behaviors as variables. The @@functions are system functions, and their syntax usage follows the rules for functions.
• You can't use variables in a view.
• Changes to variables aren't affected by the rollback of a transaction.

The following script creates a small test table and populates it with 26 rows. The script uses a variable to do three things:

• Control how many rows are inserted by controlling how many times the loop is executed.
• Supply the value inserted into the integer column.
• Function as part of the expression that generates letters to be inserted into the character column.

Declaring a Transact-SQL Variable

The DECLARE statement initializes a Transact-SQL variable by:

• Assigning a name. The name must have a single @ as the first character.
• Assigning a system-supplied or user-defined data type and a length. For numeric variables, a precision and scale are also assigned. For variables of type XML, an optional schema collection may be assigned.
• Setting the value to NULL.

For example, the following DECLARE statement creates a local variable named @mycounter with an int data type.

To declare more than one local variable, use a comma after the first local variable defined, and then specify the next local variable name and data type.

For example, the following DECLARE statement creates three local variables named @LastName , @FirstName and @StateProvince , and initializes each to NULL:

The scope of a variable is the range of Transact-SQL statements that can reference the variable. The scope of a variable lasts from the point it is declared until the end of the batch or stored procedure in which it is declared. For example, the following script generates a syntax error because the variable is declared in one batch and referenced in another:

Variables have local scope and are only visible within the batch or procedure where they are defined. In the following example, the nested scope created for execution of sp_executesql does not have access to the variable declared in the higher scope and returns and error.

Setting a Value in a Transact-SQL Variable

When a variable is first declared, its value is set to NULL. To assign a value to a variable, use the SET statement. This is the preferred method of assigning a value to a variable. A variable can also have a value assigned by being referenced in the select list of a SELECT statement.

To assign a variable a value by using the SET statement, include the variable name and the value to assign to the variable. This is the preferred method of assigning a value to a variable. The following batch, for example, declares two variables, assigns values to them, and then uses them in the WHERE clause of a SELECT statement:

A variable can also have a value assigned by being referenced in a select list. If a variable is referenced in a select list, it should be assigned a scalar value or the SELECT statement should only return one row. For example:

If there are multiple assignment clauses in a single SELECT statement, SQL Server does not guarantee the order of evaluation of the expressions. Note that effects are only visible if there are references among the assignments.

If a SELECT statement returns more than one row and the variable references a non-scalar expression, the variable is set to the value returned for the expression in the last row of the result set. For example, in the following batch @EmpIDVariable is set to the BusinessEntityID value of the last row returned, which is 1:

Declare @local_variable SET @local_variable SELECT @local_variable Expressions (Transact-SQL) Compound Operators (Transact-SQL)

Was this page helpful?

Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback .

Submit and view feedback for

Additional resources

T-SQL Tutorial

T-sql variables - declare and set variable.

In SQL Server, variables are used to store and manipulate data within a T-SQL script or a stored procedure. Variables provide a way to store temporary values that can be used in various parts of a script or procedure. They can hold different data types, such as integers, strings, dates, or other SQL Server data types.

1. What is a Variable?

A Transact-SQL local variable is an database object that can store a single data value of a specific type.

2. What are the types of variables?

The following are the two main types of SQL Server variables: Local variables . They are declared by the user and start with the '@' symbol. Local variables can be used within a procedure or batch. Global variables . They are declared by the system beforehand and start with the '@@' symbol. Global variables can store session information.

3. Declare a Transact-SQL Variable

To declare a variable uses the keyword DECLARE , assign a variable name and a data type . The DECLARE statement of Transact-SQL will declare a variable as per the instruction given by the user.

Syntax for the DECLARE statement: DECLARE @MyVariable datatype;

The following are the rules behind the DECLARE statement: A name should be assigned by having '@' before itself. A data type and length should be assigned. The most used date types are: INT , DATE , VARCHAR . Initially, the value of the variable is set to null. Examples: DECLARE @EMP_ID INT; DECLARE @EMP_ID AS INT; DECLARE @EMP_NAME VARCHAR (50); DECLARE @EMP_ID AS INT, @EMP_NAME VARCHAR (50);

4. Set a Variable Value

After a variable is declared, it gets the default NULL value. To assign a value to a variable, use the SET statement. Syntax for the SET statement to set one variable: DECLARE @Local_Variable Data_Type SET @Local_Variable = Value Example: DECLARE @EMP_ID AS INT SET @EMP_ID = 5 PRINT @EMP_ID

Syntax for the SET statement to set multiple variables: DECLARE @Local_Variable_1 Data_Type, @Local_Variable_2 Data_Type SET @Local_Variable_1 = Value SET @Local_Variable_2 = Value Example: DECLARE @EMP_ID as INT, @EMP_NAME AS VARCHAR(50) SET @EMP_ID = 5 SET @EMP_NAME = 'STEVE' PRINT @EMP_ID PRINT @EMP_NAME

5. SELECT a Variable

The SELECT statement can be used to select the assigned values by certain criteria as per the requirement of the user. Syntax for the SELECT statement of one variable or multiple variables: DECLARE @Local_Variable Data_Type SET @Local_Variable = Value Example: DECLARE @EMP_ID as INT, @EMP_NAME AS VARCHAR(50) SELECT @EMP_ID = 5, @EMP_NAME = 'STEVE' PRINT @EMP_ID PRINT @EMP_NAME

6. Complex example

USE model; GO DECLARE @count int; DECLARE @name varchar(250); DECLARE @dsp varchar(250); SET @count=0; SET @dsp='Basic of T-SQL'; select @count = count(*) from Certifications; IF @count > 0     BEGIN        select @name=name        from Certifications        where description=@dsp;        PRINT @name     END; ELSE     BEGIN        PRINT 'Empty table'     END; GO

T-SQL certification

7. Conclusion

SQL Server variables play a crucial role in writing dynamic and flexible T-SQL scripts. They provide a way to store and manipulate data, making it possible to create more versatile and reusable code. However, it's essential to use variables wisely and be mindful of their scope to avoid potential issues in larger and more complex scripts or procedures.

8. More examples

• Declare variable date
• Declare variable string
• Declare variable table

Advertisements

• SQL Server / Transact-SQL
• Oracle / PLSQL
• Web Development
• Color Picker
• Programming
• Techie Humor

SQL Server Basics

• AND & OR
• COMPARISON OPERATORS
• IS NOT NULL
• SELECT INTO

SQL Server Advanced

• Alter Login
• Alter Table
• Change Password
• Check Constraints
• Comments in SQL
• Create Login
• Create Table
• Create User
• Declare Variables
• Find Logins
• Global Temp
• Grant/Revoke Privileges
• Primary Key
• Unique Constraints

SQL Server Foreign Keys

• Disable Foreign Key
• Drop Foreign Key
• Enable Foreign Key
• Foreign Key
• Foreign Key (cascade delete)
• Foreign Key (set null)

SQL Server Loops/Conditionals

String functions.

• Concat with +

Numeric/Math Functions

Date/time functions.

• CURRENT_TIMESTAMP

Conversion Functions

• TRY_CONVERT

Configuration Functions

Advanced functions.

• CURRENT_USER
• SESSION_USER
• SESSIONPROPERTY
• SYSTEM_USER

SQL Server: Declare Variables

Learn how to declare variables in SQL Server (Transact-SQL) with syntax and examples.

What is a variable in SQL Server?

In SQL Server (Transact-SQL), a variable allows a programmer to store data temporarily during the execution of code.

The syntax to declare variables in SQL Server using the DECLARE statement is:

Parameters or Arguments

Example - declare a variable.

Let's look at an example of how to declare a variable in SQL Server.

For example:

This DECLARE statement example would declare a variable called @techonthenet that is a VARCHAR datatype, with a length of 50 characters.

You then change the value of the @techonthenet variable using the SET statement, as follows:

Next, let's also look at how to declare an INT variable in SQL Server.

To assign a value to the @site_value variable, you can use the SET statement, as follows:

This SET statement would assign the variable @site_value to the integer 10.

Example - Declare more than one variable

Let's look at how to declare more than one variable at a time in SQL Server.

In this example, we are declaring two variables. The first is the variable called @techonthenet which is defined as a VARCHAR(50) and the second is a variable called @site_value which is declared as an INT.

Example - Declare a variable with an initial value

Next, let's look at an example of how to declare a variable in SQL Server and give it an initial value.

This DECLARE statement example would declare a variable called @techonthenet that is a VARCHAR datatype with a length of 50 characters. It would then set the variable of the @techonthenet variable to 'Example showing how to declare variable'.

Finally, let's look at how to declare an INT variable in SQL Server and assign an inital value.

This variable declaration example would declare a variable called @site_value that is an INT datatype. It would then set the value of the @techonthenet variable to the integer value fo 10.

Example - Declare more than one variable with an initial value

Let's look at how to declare more than one variable and assign initial values to those variables in SQL Server.

In this example, we are declaring two variables and both of these variables are assigned initial values in their declaration.

• The first variable is called @techonthenet which is defined as a VARCHAR(50) and assigned the initial value of 'Example showing how to declare variable'.
• The second variable is called @site_value which is declared as an INT and assigned the intial value of 10.

Home | About Us | Contact Us | Testimonials | Donate

While using this site, you agree to have read and accepted our Terms of Service and Privacy Policy .

Copyright © 2003-2024 TechOnTheNet.com. All rights reserved.

SQL Variables: SQL Server Declare, Set and Select Variable

What is a Variable in SQL Server?

In MS SQL, variables are the object which acts as a placeholder to a memory location. Variable hold single data value.

Variable Types in SQL: Local, Global

MS SQL has two types of variables:

Local variable

• Global variable.

However, the user can only create a local variable.

Below figure explain two types of variable available in MS SQL server .

• A user declares the local variable.
• By default, a local variable starts with @.
• Every local variable scope has the restriction to the current batch or procedure within any given session.

Global variable

• The system maintains the global variable. A user cannot declare them.
• The global variable starts with @@
• It stores session related information .

How to DECLARE a variable in SQL

• Before using any variable in batch or procedure, you need to declare the variable.
• DECLARE command is used to DECLARE variable which acts as a placeholder for the memory location.
• Only once the declaration is made, a variable can be used in the subsequent part of batch or procedure.

TSQL Syntax:

• Initialization is an optional thing while declaring.
• By default, DECLARE initializes variable to NULL.
• Using the keyword ‘AS’ is optional.
• To declare more than one local variable, use a comma after the first local variable definition, and then define the next local variable name and data type .

Examples of Declaring a variable

Query: With ‘AS’

Query: Without ‘AS’

Query: DECLARE two variables

Assigning a value to SQL Variable

You can assign a value to a variable in the following three ways :

• During variable declaration using DECLARE keyword.
• Using SELECT

Let’s have a look at all three ways in detail:

During variable declaration using DECLARE keyword

T-SQL Syntax:

Here, after datatype we can use ‘=’ followed by value to be assigned

Using SQL SET VARIABLE

Sometimes we want to keep declaration and initialization separate. SET can be used to assign values to the variable, post declaring a variable.Below are the different ways to assign values using SET:

Example : Assigning a value to a variable using SET

Example : Assign a value to multiple variables using SET.

Rule: One SET Keyword can be used to assign a value to only one variable .

Example : Assigning a value to a variable with a Scalar Subquery using SET

• Enclose the query in parenthesis.
• The query should be a scalar query. A scalar query is a query with results as just one row and one column. Otherwise, the query will throw an error.
• If the query returns zero rows, then the variable is set to EMPTY, i.e., NULL.

Assumption: Assume that we have the table as ‘Guru99’ with two columns as displayed below:

We will use ‘Guru99’ table in the further tutorials

Example 1: When subquery return one row as a result.

Example 2: When subquery returns zero row as a result

In this particular case, the variable value is EMPTY, i.e., NULL.

Using SQL SELECT VARIABLE

Just like SET, we can also use SELECT to assign values to the variables, post declaring a variable using DECLARE. Below are different ways to assign a value using SELECT:

Example : Assigning a value to a variable using SELECT

Example : Assigning a value to multiple variable using SELECT

Rules: Unlike SET, SELECT can be used to assign a value to multiple variables separated by the comma .

Example : Assigning the value to a variable with a Subquery using SELECT

• Enclose the query in Parenthesis.
• The query should be a scalar query. The scalar query is the query with the result as one row and one column. Otherwise, the query will throw an error.
• If the query returns zero rows, then the variable is EMPTY, i.e., NULL.
• Reconsider our ‘Guru99’ table

Example 2: When subquery return zero row as a result

In this particular case, the variable is to EMPTY, i.e., NULL.

Example 3: Assign a value to a variable with a regular SELECT statement.

• Unlike SET, if the query results in multiple rows then the variable value is set to the value of the last row.

Query 1: The query returns one row.

Query 2: The query returns multiple rows.

In this special case, variable value is set to the value of the last row .

Query 3: The query returns zero rows.

In this particular case, the variable is EMPTY, i.e., NULL.

Other SQL Variable Examples

Using variable in the query

Interesting Facts About SQL Server Variables!

• A local variable can be displayed using PRINT as well as SELECT COMMAND
• Table Data type doesn’t allow the use of ‘AS’ during declaration.
• SET complies with ANSI standards whereas SELECT does not.
• Creating a local variable with the name as @ is also allowed. We can declare it as, for example: 'DECLARE @@ as VARCHAR (10)'
• Variables are the object which acts as a placeholder.
• Two types of Variable exist: Local and Global
• We can assign the variable in the following three ways: While using 1) DECLARE 2) Using SET 3) USING SELECT
• SQL Server Data Types with Examples
• CASE Statement & Nested Case in SQL Server: T-SQL Example
• Substring() in SQL Server: How to use Function with Example
• JOINS in SQL Server: Tutorial with Examples
• How to Create Login, User and Grant Permissions in SQL Server
• SQL Server Tutorial PDF for Beginners (Free Download)
• Top 40 SSIS Interview Questions and Answers (2024)
• Top 20 SSRS Interview Questions and Answers (2024)

• SQL Server training
• Write for us!

What to choose when assigning values to SQL Server variables: SET vs SELECT T-SQL statements

SQL Server provides us with two methods in T-SQL to assign a value to a previously created local SQL variable. The first method is the SET statement, the ANSI standard statement that is commonly used for variable value assignment. The second statement is the SELECT statement. In addition to its main usage to form the logic that is used to retrieve data from a database table or multiple tables in SQL Server, the SELECT statement can be used also to assign a value to a previously created local variable directly or from a variable, view or table.

Although both T-SQL statements fulfill the SQL variable value assignment task, there is a number of differences between the SET and SELECT statements that may lead you to choose one of them in specific circumstances, over the other. In this article, we will describe, in detail, when and why to choose between the SET and SELECT T-SQL statements while assigning a value to a variable.

We will start with creating a new table and fill it with few records for our demo. This can be achieved using the below script:

The inserted data can be checked using the following SELECT statement:

And the data will be shown as below:

If we manage to assign a scalar value for the SQL variable that is previously defined using the DECLARE statement, both the SET and SELECT statements will achieve the target in the same way. The below SET statement is used to assign the @EmpName1 variable with the scalar “Ali” value:

In the same way, the below SELECT statement can be used to assign the @EmpName2 variable with the scalar “Ali” value:

The assigned values for the variables in the previous queries will be printed in the Messages tab as shown below:

SQL Server allows us to assign value for a SQL variable from a database table or view. The below query is used to assign the @EmpName variable the Name column value of the third group members from the SetVsSelectDemo table using the SET statement:

The SELECT statement can be also used to perform the same assignment task in a different way as shown below:

The results of the previous two queries will be displayed in the Messages tab as shown below:

Until this point, you can see that both the SET and SELECT statements can perform the variable value assignment task in the same way and differ from the code side only.

Multiple SQL Variables

Assume that we need to assign values to multiple variables at one shot. The SET statement can assign value to one variable at a time; this means that, if we need to assign values for two variables, we need to write two SET statements. In the below example, each variable requires a separate SET statement to assign it scalar value, before printing it:

On the other hand, the SELECT statement can be used to assign values to the previously defined multiple SQL variables using one SELECT statement. The below SELECT statement can be easily used to assign scalar values to the two variables using one SELECT statement before printing it:

You can see from the printed result below, that both statements achieve the same task, with the SELECT statement better than the SET statement when trying to assign values to multiple variables due to code simplicity:

Again, if we try to assign values from database table to multiple variables, it requires us SET statements equal to the number of variables. In our example, we need two SET statements to assign values from the SetVsSelectDemo table to the @EmpName and @EmpGrade variables as shown in the script below:

On the other hand, only one SELECT statement can be used to assign values from the SetVsSelectDemo table to the @EmpName and @EmpGrade SQL variables, using simpler query as shown clearly below:

It is obvious from the previous two queries that the query that is using the SELECT statement is more efficient than the one using the SET statement when assigning values to multiple variables at the same time, due to the fact that, the SET statement can only assign one variable at a time. The similar results of the previous two queries that are printed in the Messages tab will be like the below in our case:

Multiple values

The second point, in which the difference between assigning values to the SQL variables using the SELECT or SET statements appears, is when the result set of the subquery query that is used to assign a value to the variable returns more than one value. In this case, the SET statement will return an error as it accepts only one scalar value from the subquery to assign it to the variable, while the SELECT statement accepts that situation, in which the subquery will return multiple values, without raising any error. You will not, though, have any control on which value will be assigned to the variable, where the last value returned from the subquery will be assigned to the variable.

Assume that we need to assign the Name value of the second group from the previously created SetVsSelectDemo table to the @EmpName SQL variable. Recall that the second group on that table contains two records in the result set as shown below:

The script that is used to assign the @EmpName variable value from the SetVsSelectDemo table using the SET and SELECT statements will be like:

Due to the fact that, the subquery statement returned two records, assigning value to the @EmpName SQL variable using the SET statement will fail, as the SET statement can assign only single value to the variables. This is not the case when assigning value to the @EmpName variable using the SELECT statement that will succeed with no error, assigning the name from the second returned record, which is “Zaid”, to the variable as shown in the result messages below:

We can learn from the previous result that, when you expect that the subquery will return more than one value, it is better to use the SET statement to assign value to the variable by implementing a proper error handling mechanism, rather than using the SELECT statement that will assign the last returned value to the SQL variable, with no error returned to warn us that the subquery returned multiple values.

Assign no value

Another difference between assigning values to the SQL variables using the SET and SELECT statements, is when the subquery that is used to assign a value to the variable return no value. If the previously declared variable has no initial value, both the SET and SELECT statement will act in the same way, assigning NULL value to that variable.

Assume that we need to assign the @EmpName variable, with no initial value, the Name of the fifth group from the SetVsSelectDemo table. Recall that this table has no records that belong to the fifth group as shown below:

The script that is used to assign the value to the @EmpName variable from the SetVsSelectDemo table will be like:

Having no initial value for the @EmpName variable, and no value returned from the subquery, a NULL value will be assigned to that variable in both cases as shown clearly in the result message below:

If the previously declared SQL variable has an initial value, and the subquery that is used to assign a value to the variable returns no value, the SET and SELECT statement will behave in different ways. In this case, the SET statement will override the initial value of the variable and return the NULL value. On the contrary, the SELECT statement will not override the initial value of the variable and will return it, if no value is returned from the assigning subquery.

If we arrange again to assign the @EmpName variable, the Name of the fifth group from the SetVsSelectDemo table, recalling that this table has no records that belong to the fifth group, but this time, after setting an initial value for the @EmpName SQL variable during the variable declaration, using the SET and SELECT statements, as shown in the script below:

Taking into consideration that the assigning subquery retuned no value, the query that used the SET statement to assign value to the SQL variable will override the initial value of the variable, returning NULL value, while the query that used the SELECT statement to assign value to the variable will keep the initial value with no change as no value is returned from the subquery, as shown clearly in the results below:

• If you manage to assign values to multiple variables directly or from a database table, it is better to use the SELECT statement, that requires one statement only, over the SET statement due to coding simplicity
• If you are following the ANSI standard for code migration purposes, use the SET statement for SQL variables values assignment, as the SELECT statement does not follow the ANSI standard
• If the assigning subquery returns multiple values, using the SET statement to assign value to a variable will raise an error as it only accepts a single value, where the SELECT statement will assign the last returned value from the subquery to the variable, with no control from your side
• If the assigning subquery returns no value, the SET statement will override the variable initial value to NULL, while the SELECT statement will not override its initial value
• Recent Posts

• Azure Data Factory Interview Questions and Answers - February 11, 2021
• How to monitor Azure Data Factory - January 15, 2021
• Using Source Control in Azure Data Factory - January 12, 2021

Related posts:

• Qué elegir al asignar valores a las variables de SQL Server: sentencias SET vs SELECT T-SQL
• Static Data Masking in SSMS 18
• SQL Server PRINT and SQL Server RAISERROR statements
• What is causing database slowdowns?
• SQL Variables: Basics and usage

Home » Variables

Summary : in this tutorial, you will learn about variables including declaring variables, setting their values, and assigning value fields of a record to variables.

What is a variable

A variable is an object that holds a single value of a specific type e.g., integer , date , or varying character string .

We typically use variables in the following cases:

• As a loop counter to count the number of times a loop is performed.
• To hold a value to be tested by a control-of-flow statement such as WHILE .
• To store the value returned by a stored procedure or a function

Declaring a variable

To declare a variable, you use the DECLARE statement. For example, the following statement declares a variable named @model_year :

The DECLARE statement initializes a variable by assigning it a name and a data type. The variable name must start with the @ sign. In this example, the data type of the @model_year variable is SMALLINT .

By default, when a variable is declared, its value is set to NULL .

Between the variable name and data type, you can use the optional AS keyword as follows:

To declare multiple variables, you separate variables by commas:

Assigning a value to a variable

To assign a value to a variable, you use the SET statement. For example, the following statement assigns 2018 to the @model_year variable:

Using variables in a query

The following SELECT statement uses the  @model_year variable in the WHERE clause to find the products of a specific model year:

Now, you can put everything together and execute the following code block to get a list of products whose model year is 2018:

Note that to execute the code, you click the Execute button as shown in the following picture:

The following picture shows the output:

Storing query result in a variable

The following steps describe how to store the query result in a variable:

First, declare a variable named @product_count with the integer data type:

Second, use the SET statement to assign the query’s result set to the variable:

Third, output the content of the @product_count variable:

Or you can use the PRINT statement to print out the content of a variable:

The output in the messages tab is as follows:

To hide the number of rows affected messages, you use the following statement:

Selecting a record into variables

The following steps illustrate how to declare two variables, assign a record to them, and output the contents of the variables:

First, declare variables that hold the product name and list price:

Second, assign the column names to the corresponding variables:

Third, output the content of the variables:

Accumulating values into a variable

The following stored procedure takes one parameter and returns a list of products as a string:

In this stored procedure:

• First, we declared a variable named @product_list with varying character string type and set its value to blank.
• Second, we selected the product name list from the products table based on the input @model_year . In the select list, we accumulated the product names to the @product_list variable. Note that the CHAR(10) returns the line feed character.
• Third, we used the PRINT statement to print out the product list.

The following statement executes the uspGetProductList stored procedure:

The following picture shows the partial output:

In this tutorial, you have learned about variables including declaring variables, setting their values, and assigning value fields of a record to the variables.

# Variables

# updating variables using select.

Using SELECT , you can update multiple variables at once.

When using SELECT to update a variable from a table column, if there are multiple values, it will use the last value. (Normal order rules apply - if no sort is given, the order is not guaranteed.)

If there are no rows returned by the query, the variable's value won't change:

# Declare multiple variables at once, with initial values

# declare a table variable.

When you create a normal table, you use CREATE TABLE Name (Columns) syntax. When creating a table variable, you use DECLARE @Name TABLE (Columns) syntax.

To reference the table variable inside a SELECT statement, SQL Server requires that you give the table variable an alias, otherwise you'll get an error:

Must declare the scalar variable "@TableVariableName".

# Updating a variable using SET

Using SET , you can only update one variable at a time.

# Compound assignment operators

Supported compound operators:

+= Add and assign -= Subtract and assign *= Multiply and assign /= Divide and assign %= Modulo and assign &= Bitwise AND and assign ^= Bitwise XOR and assign |= Bitwise OR and assign

Example usage:

# Updating variables by selecting from a table

Depending on the structure of your data, you can create variables that update dynamically.

In most cases, you will want to ensure that your query returns only one value when using this method.

• DECLARE @VariableName DataType [ = Value ] ;
• SET @VariableName = Value ;

← NULLs Dates →

PowerShell for the SQL Server DBA - Variable Fundamentals

By: Alejandro Cobar   |   Updated: 2022-02-10   |   Comments (2)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | > PowerShell

Moving forward with the tutorial series, PowerShell for the DBA , I would like to begin with one of the basic elements that will allow you to build scripts from the ground up, and that's variables. I know that it sounds very basic, but I think it's important to present the fundamentals on how they are declared, used, the datatypes available, and their scope within a script, so that you can write effective scripts for your DBA related tasks.

In this tutorial, we will learn how to declare, assign values and use variables in PowerShell, along with several examples to have a clearer understanding.

Initial considerations

• A very basic knowledge of coding in any other programming language will be assumed.
• No interaction with SQL Server will be covered in this tutorial, that will be covered in a future tutorial.

Important note: In the previous tutorials (see links at the end) we learned how to setup our environment of preference (Linux, MacOS, Windows) to be able to execute scripts. From this point forward I will be presenting all the examples using Windows, but that shouldn't be a showstopper for you to follow along.

Variables in General

Formally speaking, variables reside in the heap, which is a portion of memory allocated to a running program, and allocated by the Operating System. However, through them we can assign values which can either stay the same or change through the life-cycle of our program/script.

Now, an easier way to digest variables would be to think of them as individual boxes that can store something and the contents of the box can change (or not) over time.

All programming languages have their own set of rules in regards to variables, such as:

• Variable names cannot start with a number.
• If you define that a certain variable will be used to store only numbers, then you can't store strings there.
• The name assigned to your variable must be unique, within the same scope where it will be used.

These are just some examples, which can differ from language to language, so we will take a look at the rules defined for PowerShell.

Variables in Windows PowerShell

To work with variables in PowerShell, we have to do it the right way, or else we will fail to accomplish whatever it is that we're after, so let's dive in to learn how to do it appropriately.

Declaration of Variable in PowerShell

To declare a variable, you must start with a dollar sign ($) and then put the name you want for it.

Specifying Variable Data Type in PowerShell

To specify the data type of your variable, you have a couple of options: specify the data type yourself or let PowerShell figure it out for you.

Here's a list of data types that you can use:

Let's take a look at a few examples to see this in action.

Specify the data type within brackets syntax

Don't specify the data type syntax

Now, you might think: "If I don't specify the data type, how can I be sure that PowerShell will use the correct one?". Well in this case, based on the value assigned to the variable, PowerShell will automatically figure out what data type to use, but you can also check for yourself just to be 100% sure.

Here's how to check it:

Note: Don't worry about that "weird looking" command .GetType().Name. Eventually, we will cover functions, properties and all the good stuff that PowerShell allows us to use.

Now, what would happen if you choose not to specify the data type of your variable and decide to store different data type values within your script? Let's find out:

So, in this particular case, PowerShell sets the data type of the variable the very first time it encounters a value in the PowerShell session and sticks with it.

Now, let's evaluate a few additional scenarios just to see how PowerShell reacts.

Let's see what happens in the following example if I try to use the same variable to store values from different data types within the same script, but explicitly specifying the data types.

This does not return an error, just like the one from our previous example. In this case, PowerShell will stick to the String data type (in double quotes) from this point forward, during the whole lifecycle of the script, unless another explicit data type assignation takes place at some point within the script (it will basically forget that the variable was initially an integer).

Let's see what happens if I try to omit the first data type assignation, but specify a data type for the new variable in the second one.

Hold on, this doesn't make sense because the first declaration should return Int32, based on the fact that we know that PowerShell deduces the data type based on the value assigned right? Well, this is a tricky one that requires a bit of explaining from my side. See, I'm using PowerShell ISE to code this sample script and run it to see its output, but doing so in that way leaves the contents of the memory intact, in regards to my variables within my script, so the data type stored in memory will be String unless I either specify a data type for each variable declaration, or I close PowerShell ISE, fire it up again and run my script.

Therefore, let me close PowerShell ISE, open it up and execute my script a few times to see what it does:

See, the very first time it behaved "correctly", according to what we have learned so far, but the subsequent executions tell a different story (but now you know why it is doing this).

It is very important to note that this behavior will not be the same if you execute your script using the other options that we covered in the previous article, so let's give it a shot a couple of times to confirm this:

As you can see, either from the PowerShell console or the Command Prompt, the values are always the same because the contents of the memory, related to my script, are wiped as soon as the execution ends.

I know that I might have extended a bit on something relatively trivial, but it is very important that you are aware of these tiny details that might make your script do unexpected things. Now, to close this part, I'd just like to show you how to address this specific issue using the PowerShell ISE.

So, by explicitly defining the data type for each value assignation, it won't show the "weird" behavior that we saw before.

With all of this out of the way, let's continue exploring relevant information to properly work with variables in PowerShell.

PowerShell variables are not case-sensitive

Variables are not case-sensitive, so PowerShell will not differentiate between $myFirstVariable and $myfirstvariable.

Regardless of the lack of case-sensitivity while naming variables, you should still be careful while using PowerShell ISE because the same "weird" behavior might be experienced.

Assign Value to PowerShell Variable

You can assign a value to a variable using either a direct value or an assignment operator.

Assignment operators can be one of the following (taken from Microsoft's official documentation ). The documentation includes a wide variety of examples that you can check out on your own, to expand what's being detailed within this article.

Here are some examples of different ways a value can be assigned to a variable.

For naming variables, you can make this complicated a bit if you like to use special characters, or names with spaces, something which is odd in practically the vast majority of languages out there.

As you can see, simply wrap the name of your variable within curly braces and you're good to go.

You can work with arrays pretty easily, as you will see in the example below:

Same as with arrays, you can work with hash tables pretty easily:

Scope of PowerShell Variables

What does scope of a variable mean? Well, a simple way to describe it would be in terms of the "visibility" of my variable within my script. However, I think this can be easily digested through some examples, so let's take a look at some.

Calling the exact same variable in two different functions within the same script.

In my script, I have defined 2 functions (we will cover functions in more depth in another article):

• Function f1 will display text and the value of the variable if the value of $myVariable is set, or it will display "I have nothing…" if the variable is empty.
• Function f2 simply assigns the string value "dummyValue" to $myVariable.

After defining both functions, I call them in the order depicted in the screenshot, and the output is what's interesting. The first time I call f1 it will display "I have nothing…" because $myVariable is empty, which is correct; however, after calling f2, which assigns a value to the variable, and then calling f1 again, then the result is exactly the same. This is because $myVariable is only visible within each individual function, at least in this particular example.

Let's try to address this using a special keyword available in PowerShell.

By introducing the keyword $global for my function f2, I'm telling PowerShell that the scope of $myVariable is global, which means that it will be visible in any part of my script. Now, as you can see in my example, I'm calling the functions in the exact same order as before, but the output is totally different. The second call of function f1 understands now that $myVariable has a value that was set in f2 and it displays it correctly.

Now that we've covered how to specify a global scope for a variable, we can also specify a local scope for it, either by using the keyword $local or by omitting it (just like the first example I showed). There are also a few additional keywords that can be used to specify the scope of a variable, but I won't be covering them in this article, since the usage of global and local scopes should be more than enough, at least initially, to use PowerShell for DBA related tasks.

• You can go through Microsoft's official documentation if you'd like to take a deeper dive on variable scope in PowerShell.
• Variables can seem to be a trivial thing in any programming language, but you must be familiar with all the details around them so that you can craft effective scripts.
• Even if PowerShell is relatively flexible with the fact that it lets you create variables with special characters and/or white spaces, you should try not to complicate things too much or your code might be a bit challenging to work with (especially for other people).
• Be as descriptive as you can with the naming of your variables, not just for PowerShell, but for any programming/scripting language that you decide to work with.
• Even if PowerShell lets you omit the data type for your variables, because it can figure it out automatically, I strongly recommend explicitly specifying it. It will make your code way more predictable and you will definitely avoid weird things like the example I showed you while working with PowerShell ISE.
• In the next tutorial of this series, we will be taking a look at if/else blocks and functions, so stay tuned!
• PowerShell Commands for SQL Server – Getting Started
• PowerShell for the SQL Server DBA – Environment Setup

About the author

Comments For This Article

Related Content

PowerShell for the DBA - Basic Functions

PowerShell for the DBA - CMDLETs and Advanced Functions

PowerShell for the SQL Server DBA - Pipelines

PowerShell for the DBA - Performing DBA tasks using SQL Server CMDLETs

PowerShell for the DBA - If Else and Switch statements

PowerShell for the DBA – FOR and WHILE Loops

PowerShell for the SQL Server DBA – Environment Setup

Related Categories

Azure Data Studio

SQL Operations Studio

SQL Server Agent

SQL Server Management Objects SMO

SQL Server Management Studio

SQL Server Management Studio Configuration

SQL Server Management Studio Shortcuts

Development

Date Functions

System Functions

JOIN Tables

Database Administration

Performance

Performance Tuning

Locking and Blocking

Data Analytics \ ETL

Microsoft Fabric

Azure Data Factory

Integration Services

Popular Articles

SQL Date Format Options with SQL CONVERT Function

SQL Date Format examples using SQL FORMAT Function

SQL Server CROSS APPLY and OUTER APPLY

SQL Server Cursor Example

SQL CASE Statement in Where Clause to Filter Based on a Condition or Expression

DROP TABLE IF EXISTS Examples for SQL Server

SQL Convert Date to YYYYMMDD

Rolling up multiple rows into a single row and column for SQL Server data

SQL NOT IN Operator

Resolving could not open a connection to SQL Server errors

Format numbers in SQL Server

SQL Server PIVOT and UNPIVOT Examples

Script to retrieve SQL Server database backup history and no backups

How to install SQL Server 2022 step by step

An Introduction to SQL Triggers

Using MERGE in SQL Server to insert, update and delete at the same time

How to monitor backup and restore progress in SQL Server

List SQL Server Login and User Permissions with fn_my_permissions

SQL Server Loop through Table Rows without Cursor

SQL Server Database Stuck in Restoring State