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

  • SQL Server training
  • Write for us!

Ahmad Yaseen

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:

ms sql variable assignment

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:

ms sql variable assignment

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:

the result of SQL variable query

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:

output of select statement

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:

Printed message of executed uery

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:

Select statement output

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:

Subquery error message with variable

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:

Output of select statement

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:

NULL values in a variable

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:

Subquery and NULL statement values

  • 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

Ahmad Yaseen

  • 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

Guru99

SQL Variables: SQL Server Declare, Set and Select Variable

Richard Peterson

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 .

SQL Variables

  • 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

Assigning a value to SQL Variable

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

SQL SET VARIABLE

Example : Assign a value to multiple variables using SET.

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

SQL SET 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:

SQL SET VARIABLE

We will use ‘Guru99’ table in the further tutorials

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

SQL SET VARIABLE

Example 2: When subquery returns zero row as a result

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

SQL SET VARIABLE

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

SQL SELECT VARIABLE

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 .

SQL SELECT VARIABLE

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

SQL SELECT VARIABLE

Example 2: When subquery return zero row as a result

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

SQL SELECT VARIABLE

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.

SQL SELECT VARIABLE

Query 2: The query returns multiple rows.

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

SQL SELECT VARIABLE

Query 3: The query returns zero rows.

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

SQL SELECT VARIABLE

Other SQL Variable Examples

Using variable in the query

Other SQL Variable

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)

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:

Stored Procedure Variables - execute a code block

The following picture shows the output:

Stored Procedure Variables - 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:

Stored Procedure Variables - assign a record to a variable

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:

Stored Procedure Variables - Stored Procedure Example

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 →

SQL Variables for Queries and Stored Procedures in SQL Server, Oracle and PostgreSQL

By: Andrea Gnemmi   |   Updated: 2022-09-19   |   Comments   |   Related: More > Other Database Platforms

We all know how convenient it is to use SQL variables in queries and stored procedures, but there are big differences in the SQL statement syntax and use of variables for Microsoft SQL Server, Oracle and PostgreSQL which we will cover in this tutorial.

In this tutorial we will review the different ways to declare and use variables and placeholders in SQL Server, Oracle and PostgreSQL. We will also see the various differences in syntax and logic as well as types of variables for various SQL databases.

As always, we will use the github freely downloadable database sample Chinook, as it is available in multiple RDBMS formats . It is a simulation of a digital media store, with some sample data, all you have to do is download the version you need and you have all the scripts for data structure and all the inserts for data.

Declaring and Using SQL Variables for Queries

In SQL Server we can define variables, also known as local variables, both in ad hoc queries and in Stored Procedures with T-SQL logic. The process is quite straightforward using the DECLARE clause in both cases and the variables are identified by a preceding "@" plus the variable name.

Let's look at a quick SELECT statement in the following example using variables in a query where we need to calculate the total purchased by customer for the first half of 2012.

query results

AsAs you can see, there are two declare statements for the two variables with DATE data type and assigned values to them. I then used those variables in the WHERE clause instead of typing the dates. The obvious advantage of doing that is that if we're going to use those dates in more than one place or change them, we just need to modify the value assigned to the local variable. Quite easy!

In Oracle is possible to define variables in queries, procedures and packages with some differences between the way in which it can be done.

First of all, we can have binding variables that are identified by a preceding ":".

In an ad hoc query they are used more as placeholders, and we do not need to use the DECLARE clause like in SQL Server.

As you can see I have inserted the :Start_date and :End_date as binding variables in the WHERE clause of the query (with a formatting for date) and when I run it I am prompted to insert the values.

enter binds

There is also another syntax supported by queries that is much more similar to SQL Server.

In this case I declare the variables with the DEFINE clause and assign them a value on the same line just like in SQL Server, then I used them in the WHERE clause, this time preceding them with an "&". Notice that in the declaration phase the "&" is not needed.

query results

Both syntaxes and ways are supported in queries without the need of a PL/SQL block. In fact inside a PL/SQL block it is not possible to use a variable in order to filter data as we have just done above, because inside a block it is expected that the query assigns values to the variables with a SELECT .. INTO type of query, let's do an example extracting a specific customer First and Last name and return it using the DBMS_OUTPUT clause as I did in this tip: INSERT INTO for SQL Server, Oracle and PostgreSQL returning the value assigned to a variable.

query results

It is also possible to declare the variables as the same data type as a column in a table, in this way the variables are "anchored" to the column data type: if it changes the variable will also change.

query results

In PostgreSQL there is not the possibility to use variables in an ad hoc query (unless using or more properly abusing CTEs) but only in PL/pgSQL blocks, such as functions/procedures, but also as in line code blocks. As in the other RDBMS this is done with the clause DECLARE. Let's do the same example.

query results

Here we have a few things to note: first, in order to have an inline code block I had to use the DO $$ and END $$ in order to delimitate the block. Second, I had to assign the rows returned by the query to a temporary table in order to present it, otherwise I would have had an error. Finally, the variables are not identified by special characters, but they can be declared and assigned in the same row as in the other RDBMS.

Variable Declaration in SQL Queries and Stored Procedures

So far we have seen how to declare and use variables in queries or code blocks similar to ad hoc queries, let's see now how they behave in procedures.

In SQL Server, as I pointed out at the beginning, the variables are treated the same way for ad hoc queries and stored procedures. Let's do a quick and easy example where we need to write a stored procedure returning the First Name and Last Name of a customer given its CustomerId.

I created a very simple stored procedure here, using the variable @CustomerId as the input parameter, now we execute it as follows.

query results

Pay attention though, in this example I have not used a local variable but instead a parameter, so let's imagine that in this stored procedure we need to also check VIP customers which are identified by the SupportRepId being the sales manager. So I modify the stored procedure as follows.

In this case I've declared two local variables: @RepId stores the value of column SupportRepId returned with a query and @Vip stores the EmployeeId of the actual Sales Manager. Let's try it out.

query results

Let's create the same procedure in Oracle, already considering the second one with the internal variables and IF logic.

Please notice that we do not need the DECLARE clause for the variables and the different syntax for the IF THEN ELSE.

In order to return the procedure's output, we have to assign it to a variable and run the same DBMS_OUTPUT.PUT_LINE that we've used before.

query results

We can do the same in PostgreSQL, but in order to return a value from the procedure we need to use a FUNCTION and the syntax here is slightly different.

So a few comments on the syntax. In order to return a result set in a PostgreSQL function we need to specify the RETURNS table clause, then we assign the value of the select query directly to the variable repid with a syntax slightly different from both SQL Server and Oracle. Last but not least, we return the result set with the RETURN QUERY clause. Notice that we need to specify the language as PLPGSQL in order to make use of the IF THEN ELSE cycle.

We can then execute the function with a simple SELECT.

query results

In this tutorial we reviewed various way to declare and use variables in SQL Server, Oracle and PostgreSQL. We also looked at differences in procedure syntax. Stay turned for future tutorials on stored procedures, system variables, dynamic SQL and more.

  • SQL Server Variables (Transact-SQL)
  • Oracle Variables and Oracle Define Variables
  • PostgreSQL Declarations
  • SQL Server Table Variable Example
  • SQL Server INSERT command with variables
  • The T-SQL DELETE statement
  • SQL Server Bit Data Type
  • How to use @@ROWCOUNT in SQL Server
  • SQL Server Stored Procedure Input Parameter, Output Parameter and Return Value

sql server categories

About the author

MSSQLTips author Andrea Gnemmi

Comments For This Article

Related articles.

ms sql variable assignment

Announcing TypeScript 5.4 RC

ms sql variable assignment

Daniel Rosenwasser

February 22nd, 2024 0 0

Today we’re excited to announce our Release Candidate of TypeScript 5.4! Between now and the stable release of TypeScript 5.4, we expect no further changes apart from critical bug fixes.

To get started using the RC, you can get it through NuGet , or through npm with the following command:

Here’s a quick list of what’s new in TypeScript 5.4!

Preserved Narrowing in Closures Following Last Assignments

The noinfer utility type, object.groupby and map.groupby, support for require() calls in --moduleresolution bundler and --module preserve, checked import attributes and assertions, quick fix for adding missing parameters.

  • Upcoming 5.5 Deprecations

Notable Behavioral Changes

What’s new since the beta.

Since the beta, we’ve updated the release notes to document new notable behavioral changes , including new restrictions around enum compatibility, new restrictions on enum member naming, and mapped types preservation improvements.

TypeScript can usually figure out a more specific type for a variable based on checks that you might perform. This process is called narrowing.

One common pain-point was that these narrowed types weren’t always preserved within function closures.

Here, TypeScript decided that it wasn’t "safe" to assume that url was actually a URL object in our callback function because it was mutated elsewhere; however, in this instance, that arrow function is always created after that assignment to url , and it’s also the last assignment to url .

TypeScript 5.4 takes advantage of this to make narrowing a little smarter. When parameters and let variables are used in non- hoisted functions, the type-checker will look for a last assignment point. If one is found, TypeScript can safely narrow from outside the containing function. What that means is the above example just works now.

Note that narrowing analysis doesn’t kick in if the variable is assigned anywhere in a nested function. This is because there’s no way to know for sure whether the function will be called later.

This should make lots of typical JavaScript code easier to express. You can read more about the change on GitHub .

When calling generic functions, TypeScript is able to infer type arguments from whatever you pass in.

One challenge, however, is that it is not always clear what the "best" type is to infer. This might lead to TypeScript rejecting valid calls, accepting questionable calls, or just reporting worse error messages when it catches a bug.

For example, let’s imagine a createStreetLight function that takes a list of color names, along with an optional default color.

What happens when we pass in a defaultColor that wasn’t in the original colors array? In this function, colors is supposed to be the "source of truth" and describe what can be passed to defaultColor .

In this call, type inference decided that "blue" was just as valid of a type as "red" or "yellow" or "green" . So instead of rejecting the call, TypeScript infers the type of C as "red" | "yellow" | "green" | "blue" . You might say that inference just blue up in our faces!

One way people currently deal with this is to add a separate type parameter that’s bounded by the existing type parameter.

This works, but is a little bit awkward because D probably won’t be used anywhere else in the signature for createStreetLight . While not bad in this case , using a type parameter only once in a signature is often a code smell.

That’s why TypeScript 5.4 introduces a new NoInfer<T> utility type. Surrounding a type in NoInfer<...> gives a signal to TypeScript not to dig in and match against the inner types to find candidates for type inference.

Using NoInfer , we can rewrite createStreetLight as something like this:

Excluding the type of defaultColor from being explored for inference means that "blue" never ends up as an inference candidate, and the type-checker can reject it.

You can see the specific changes in the implementing pull request , along with the initial implementation provided thanks to Mateusz Burzyński !

TypeScript 5.4 adds declarations for JavaScript’s new Object.groupBy and Map.groupBy static methods.

Object.groupBy takes an iterable, and a function that decides which "group" each element should be placed in. The function needs to make a "key" for each distinct group, and Object.groupBy uses that key to make an object where every key maps to an array with the original element in it.

So the following JavaScript:

is basically equivalent to writing this:

Map.groupBy is similar, but produces a Map instead of a plain object. This might be more desirable if you need the guarantees of Map s, you’re dealing with APIs that expect Map s, or you need to use any kind of key for grouping – not just keys that can be used as property names in JavaScript.

and just as before, you could have created myObj in an equivalent way:

Note that in the above example of Object.groupBy , the object produced uses all optional properties.

This is because there’s no way to guarantee in a general way that all the keys were produced by groupBy .

Note also that these methods are only accessible by configuring your target to esnext or adjusting your lib settings. We expect they will eventually be available under a stable es2024 target.

We’d like to extend a thanks to Kevin Gibbons for adding the declarations to these groupBy methods .

TypeScript has a moduleResolution option called bundler that is meant to model the way modern bundlers figure out which file an import path refers to. One of the limitations of the option is that it had to be paired with --module esnext , making it impossible to use the import ... = require(...) syntax.

That might not seem like a big deal if you’re planning on just writing standard ECMAScript import s, but there’s a difference when using a package with conditional exports .

In TypeScript 5.4, require() can now be used when setting the module setting to a new option called preserve .

Between --module preserve and --moduleResolution bundler , the two more accurately model what bundlers and runtimes like Bun will allow, and how they’ll perform module lookups. In fact, when using --module preserve , the bundler option will be implicitly set for --moduleResolution (along with --esModuleInterop and --resolveJsonModule )

Under --module preserve , an ECMAScript import will always be emitted as-is, and import ... = require(...) will be emitted as a require() call (though in practice you may not even use TypeScript for emit, since it’s likely you’ll be using a bundler for your code). This holds true regardless of the file extension of the containing file. So the output of this code:

should look something like this:

What this also means is that the syntax you choose directs how conditional exports are matched. So in the above example, if the package.json of some-package looks like this:

TypeScript will resolve these paths to [...]/some-package/esm/foo-from-import.mjs and [...]/some-package/cjs/bar-from-require.cjs .

For more information, you can read up on these new settings here .

Import attributes and assertions are now checked against the global ImportAttributes type. This means that runtimes can now more accurately describe the import attributes

This change was provided thanks to Oleksandr Tarasiuk .

TypeScript now has a quick fix to add a new parameter to functions that are called with too many arguments.

A quick fix being offered when someFunction calls someHelperFunction with 2 more arguments than are expected.

This can be useful when threading a new argument through several existing functions, which can be cumbersome today.

This quick fix was provided courtsey of Oleksandr Tarasiuk .

Upcoming Changes from TypeScript 5.0 Deprecations

TypeScript 5.0 deprecated the following options and behaviors:

  • target: ES3
  • noImplicitUseStrict
  • keyofStringsOnly
  • suppressExcessPropertyErrors
  • suppressImplicitAnyIndexErrors
  • noStrictGenericChecks
  • prepend in project references
  • implicitly OS-specific newLine

To continue using them, developers using TypeScript 5.0 and other more recent versions have had to specify a new option called ignoreDeprecations with the value "5.0" .

However, TypScript 5.4 will be the last version in which these will continue to function as normal. By TypeScript 5.5 (likely June 2024), these will become hard errors, and code using them will need to be migrated away.

For more information, you can read up on this plan on GitHub , which contains suggestions in how to best adapt your codebase.

This section highlights a set of noteworthy changes that should be acknowledged and understood as part of any upgrade. Sometimes it will highlight deprecations, removals, and new restrictions. It can also contain bug fixes that are functionally improvements, but which can also affect an existing build by introducing new errors.

lib.d.ts Changes

Types generated for the DOM may have an impact on type-checking your codebase. For more information, see the DOM updates for TypeScript 5.4 .

More Accurate Conditional Type Constraints

The following code no longer allows the second variable declaration in the function foo .

Previously, when TypeScript checked the initializer for second , it needed to determine whether IsArray<U> was assignable to the unit type false . While IsArray<U> isn’t compatible any obvious way, TypeScript looks at the constraint of that type as well. In a conditional type like T extends Foo ? TrueBranch : FalseBranch , where T is generic, the type system would look at the constraint of T , substitute it in for T itself, and decide on either the true or false branch.

But this behavior was inaccurate because it was overly-eager. Even if the constraint of T isn’t assignable to Foo , that doesn’t mean that it won’t be instantiated with something that is. And so the more correct behavior is to produce a union type for the constraint of the conditional type in cases where it can’t be proven that T never or always extends Foo.

TypeScript 5.4 adopts this more accuratre behavior. What this means in practice is that you may begin to find that some conditional type instances are no longer compatible with their branches.

You can read about the specific changes here .

More Aggressive Reduction of Intersections Between Type Variables and Primitive Types

TypeScript now reduces intersections with type variables and primitives more aggressively, depending on how the type variable’s constraint overlaps with those primitives.

For more information, see the change here .

Improved Checking Against Template Strings with Interpolations

TypeScript now more accurately checks whether or not strings are assignable to the placeholder slots of a template string type.

This behavior is more desirable, but may cause breaks in code using constructs like conditional types, where these rule changes are easy to witness.

See this change for more details.

Errors When Type-Only Imports Conflict with Local Values

Previously, TypeScript would permit the following code under isolatedModules if the import to Something only referred to a type.

However, it’s not safe for a single-file compilers to assume whether it’s "safe" to drop the import , even if the code is guaranteed to fail at runtime. In TypeScript 5.4, this code will trigger an error like the following:

The fix should be to either make a local rename, or, as the error states, add the type modifier to the import:

See more information on the change itself .

New Enum Assignability Restrictions

When two enums have the same declared names and enum member names, they were previously always considered compatible; however, when the values were known, TypeScript would silently allow them to have differing values.

TypeScript 5.4 tightens this restriction by requiring the values to be identical when they are known.

Additionally, there are new restrictions for when one of the enum members does not have a statically-known value. In these cases, the other enum must at least be implicitly numeric (e.g. it has no statically resolved initializer), or it is explicitly numeric (meaning TypeScript could resolve the value to something numeric). Practically speaking, what this means is that string enum members are only ever compatible with other string enums of the same value.

For more information, see the pull request that introduced this change .

Name Restrictions on Enum Members

TypeScript no longer allows enum members to use the names Infinity , -Infinity , or NaN .

See more details here .

Better Mapped Type Preservation Over Tuples with any Rest Elements

Previously, applying a mapped type with any into a tuple would create an any element type. This is undesirable and is now fixed.

For more information, see the fix along with the follow-on discussion around behavioral changes and further tweaks .

Emit Changes

While not a breaking change per-se, developers may have implicitly taken dependencies on TypeScript’s JavaScript or declaration emit outputs. The following are notable changes.

  • Preserve type parameter names more often when shadowed
  • Move complex parameter lists of async function into downlevel generator body
  • Do not remove binding alias in function declarations
  • ImportAttributes should go through the same emit phases when in an ImportTypeNode

What’s Next?

At this point, we anticipate very few changes to TypeScript 5.4 apart from critical bug fixes and minor bug fixes on our language service. In the next few weeks, we’ll be releasing the first stable version of TypeScript 5.4. Keep an eye on our iteration plan for target release dates and more if you need to plan around that.

Otherwise, our main focus is on developing TypeScript 5.5, and we’ve just made the TypeScript 5.5 Iteration Plan public as well. Similarly, that iteration plan describes our planned focus areas and work items, and target release dates which you can use to for your own plans. On top of that, we make it easy to use nightly builds of TypeScript on npm , and there is an extension to use those nightly releases in Visual Studio Code .

So give the RC or our nightlies a try, and let us know how it goes!

Happy Hacking!

– Daniel Rosenwasser and the TypeScript Team

ms sql variable assignment

Daniel Rosenwasser Senior Program Manager, TypeScript

ms sql variable assignment

Leave a comment Cancel reply

Log in to join the discussion.

light-theme-icon

Insert/edit link

Enter the destination URL

Or link to existing content

IMAGES

  1. SQL Variables: Basics and usage

    ms sql variable assignment

  2. How to Use SQL Variables in Queries

    ms sql variable assignment

  3. SQL Variables

    ms sql variable assignment

  4. This article explores the SQL variables using SET and Select SQL

    ms sql variable assignment

  5. SELECT vs SET For Variable Assignment In SQL Server

    ms sql variable assignment

  6. An overview of the SQL table variable

    ms sql variable assignment

VIDEO

  1. MS SQL tutorial on nested selects and variables

  2. SQL

  3. SQL: #21) Sql Function

  4. SQL: #12) Order by

  5. Chapter06-Basic SQL-15 Additional features of SQL

  6. 26 Show SQL Lab

COMMENTS

  1. Variables (Transact-SQL)

    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. SQL -- Create the table.

  2. How to set variable from a SQL query?

    To ASSIGN variables using a SQL select the best practice is as shown below->DECLARE co_id INT ; ->DECLARE sname VARCHAR(10) ; ->SELECT course_id INTO co_id FROM course_details ; ->SELECT student_name INTO sname FROM course_details; IF you have to assign more than one variable in a single line you can use this same SELECT INTO

  3. SQL Variables: Basics and usage

    SQL Server offers two different methods to assign values into variables except for initial value assignment. The first option is to use the SET statement and the second one is to use the SELECT statement. In the following example, we will declare a variable and then assign a value with the help of the SET statement: 1 2 3

  4. When to use SET vs SELECT for assigning SQL Server Variables

    SET and SELECT may be used to assign values to variables through T-SQL. Both fulfill the task, but in some scenarios unexpected results may be produced. In this tip I elaborate on the considerations for choosing between the SET and SELECT methods for assigning a value to variable. Solution

  5. SQL Variables for T-SQL Code and Queries

    The SQL variable syntax above requires the following: @local_variable: Provide a variable name, which must start with an "@" sign.; data_type: Define the data type (int, char, varchar, decimal, numeric, datetime, etc.) for the declared variable.You cannot assign the data types to be a "text", "ntext", or "image" types.= value: This is optional, as you can set a variable value in another way.

  6. SQL Variable Examples in Stored Procedures, Functions, Scripts, SQLCMD

    The following example will set a variable named tablename with the value of humanresources.department and then will do a select * to that variable. 1> :setvar tablename humanresources.department 1> select * from $ (tablename) 2> go. If everything is OK, you will be able to see the data in that table.

  7. This article explores the SQL variables using SET and Select SQL

    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.

  8. SQL Variables: SQL Server Declare, Set and Select Variable

    By : Richard Peterson Updated November 21, 2023 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. Table of Content: What is a Variable in SQL Server? Variable Types in SQL: Local, Global How to DECLARE a variable in SQL

  9. Variables in SQL Server Stored Procedures

    To assign a value to a variable, you use the SET statement. For example, the following statement assigns 2018 to the @model_year variable: SET @model_year = 2018 ; Code language: SQL (Structured Query Language) (sql) Using variables in a query

  10. Microsoft SQL Server

    CREATE TABLE #Test (Example INT) INSERT INTO #Test VALUES (1), (2) DECLARE @Variable INT SELECT @Variable = Example FROM #Test ORDER BY Example ASC PRINT @Variable. 2. SELECT TOP 1 @Variable = Example FROM #Test ORDER BY Example ASC PRINT @Variable. 1. If there are no rows returned by the query, the variable's value won't change:

  11. Assigning a variable conditionally inside a SELECT statement on SQL

    1 Answer Sorted by: 2 You can't assign in the WHERE clause. You can in the SELECT clause: DECLARE @tmpvar varchar (5) .... SELECT @tmpvar = CASE WHEN (some condition here) THEN 'YES' ELSE 'NO' END, .... FROM some_table WHERE .... But only if you're not also attempting data retrieval at the same time.

  12. SQL Declare Variable Code Examples

    It is helpful to manipulate data within a stored procedure, function, or batch of SQL statements. The variable is beneficial to keep the temporary data for the query batch. You can assign a static value or define dynamic values using SQL query. Declaring a Single SQL Variable. The T-SQL syntax for declaring a variable in SQL Server is as follows:

  13. sql

    5 Answers Sorted by: 449 Quote, which summarizes from this article: SET is the ANSI standard for variable assignment, SELECT is not. SET can only assign one variable at a time, SELECT can make multiple assignments at once. If assigning from a query, SET can only assign a scalar value.

  14. Learn SQL Variables for SQL Server, Oracle and PostgreSQL

    In this tutorial we will review the different ways to declare and use variables and placeholders in SQL Server, Oracle and PostgreSQL. We will also see the various differences in syntax and logic as well as types of variables for various SQL databases. As always, we will use the github freely downloadable database sample Chinook, as it is ...

  15. Announcing TypeScript 5.4 RC

    To get started using the RC, you can get it through NuGet, or through npm with the following command: npm install -D typescript@rc. Here's a quick list of what's new in TypeScript 5.4! Preserved Narrowing in Closures Following Last Assignments. The NoInfer Utility Type. Object.groupBy and Map.groupBy.

  16. sql

    1 Variable declaration can also be; DECLARE @V1 VarType, @V2 VarType,... Assignment; SELECT @V1 = C1, @V2 = C2,...@Vn = Cn FROM [Table] WHERE Conditions Share Follow

  17. Assign values to multiple variables from one select statement MS SQL

    sql server - Assign values to multiple variables from one select statement MS SQL - Stack Overflow Assign values to multiple variables from one select statement MS SQL Ask Question Asked 9 years, 3 months ago Modified 9 years, 3 months ago Viewed 5k times 4 The code below works. Can this be improved?