Variables (Transact-SQL)

  • Article
  • 5 minutes to read

Applies to: yesSQL Waiter (all supported versions) YesChromatic SQL Database YesBright blue SQL Managed Instance yesAzure Synapse Analytics yesAnalytics Chopine Scheme (PDW)

A Transact-SQL local protean is an object that can adjudge a single data value of a specific case. 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 prevail a data value to make up tested aside a control-of-flow affirmation.
  • To save a information value to make up returned by a stored function recall code or function return note value.

Take down

  • The name calling of some Transact-SQL system functions begin with two at signs (@@). Although in earlier versions of SQL Server, the @@functions are referred to equally global variables, @@functions aren't variables, and they don't have the same behaviors as variables. The @@functions are system functions, and their sentence structure 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 dealings.

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

  • Control how many rows are inserted by dominant how many times the loop is executed.
  • Supply the value inserted into the integer column.
  • Function as share of the expression that generates letters to atomic number 4 inserted into the character chromatography column.
            -- Create the prorogue. CREATE TABLE TestTable (cola INT, colb CHAR(3)); GO Hardening NOCOUNT Along; GO -- Declare the variant to be used. DECLARE @MyCounter INT;  -- Initialize the variable. Determined @MyCounter = 0;  -- Mental testing the inconstant to see if the loop is through. WHILE (@MyCounter < 26) BEGIN;    -- Enclose a row into the table.    INSERT INTO TestTable VALUES        -- Use the multivariate to provide the integer value        -- for cola. Also use it to father a uncomparable letter of the alphabet        -- for each row. Use the American Standard Code for Information Interchange function to bugger off the        -- integer value of 'a'. Add @MyCounter. Use Sear to        -- change over the sum bet on to the character @MyCounter        -- characters later 'a'.        (@MyCounter,         CHAR( ( @MyCounter + ASCII('a') ) )        );    -- Increment the variable to count this looping    -- of the loop-the-loop.    SET @MyCounter = @MyCounter + 1; Final stage; GO Located NOCOUNT OFF; GO -- View the data. Prime cola, colb FROM TestTable; GO Omit Set back TestTable; GO

Declaring a Transact-SQL Variable

The DECLARE argument initializes a Transact-SQL variable past:

  • Assigning a figure. The name must throw a mateless @ as the first character.
  • Assignment a arrangement-supplied operating theater user-defined information type and a length. For quantitative variables, a precision and surmount are also assigned. For variables of eccentric XML, an nonobligatory schema collection may be assigned.
  • Setting the value to NULL.

E.g., the succeeding DECLARE program line creates a topical anaestheti unsettled named @mycounter with an int information type.

            DECLARE @MyCounter INT;

To announce more than cardinal local variable, use a comma butterfly afterwards the first local variable defined, and then peg down the next local varying epithet and information case.

For instance, the following Hold assertion creates tercet local variables named @LastName, @FirstName and @StateProvince, and initializes each to Invalid:

            DECLARE @LastName NVARCHAR(30), @FirstName NVARCHAR(20), @StateProvince NCHAR(2);

The setting of a variable is the place of Transact-SQL statements that give notice cite the variable. The scope of a variable lasts from the point it is declared until the end of the clutch Oregon stored procedure in which IT is stated. E.g., the following book generates a syntax error because the inconstant is declared in one batch and documented in another:

            Exercise AdventureWorks2014; GO DECLARE @MyVariable INT; Determined @MyVariable = 1; -- Send away the batch by using the Rifle keyword. GO  -- @MyVariable has gone out of scope and no yearner exists.  -- This SELECT statement generates a syntax error because it is -- no more ratified to reference @MyVariable. SELECT BusinessEntityID, NationalIDNumber, JobTitle FROM HumanResources.Employee WHERE BusinessEntityID = @MyVariable;

Variables ingest local scope and are alone visible inside the batch or procedure where they are outlined. In the following example, the nested scope created for implementation of sp_executesql does not have memory access to the variable declared in the high scope and returns and error.

            DECLARE @MyVariable INT; SET @MyVariable = 1; EXECUTE sp_executesql N'SELECT @MyVariable'; -- this produces an error

Setting a Note 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 Unmoving statement. This is the desirable method acting of assignment a value to a variable. A variable can buoy also have a esteem assigned aside beingness documented in the select list of a SELECT statement.

To allot a inconsistent a value by using the Bent 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 favourable raft, for instance, declares two variables, assigns values to them, and so uses them in the WHERE clause of a SELECT command:

            USE AdventureWorks2014; Pass away -- Declare two variables. DECLARE @FirstNameVariable NVARCHAR(50),    @PostalCodeVariable NVARCHAR(15);  -- Set their values. SET @FirstNameVariable = N'Amy'; SET @PostalCodeVariable = N'BA5 3HX';  -- Consumption them in the WHERE clause of a SELECT statement. SELECT LastName, FirstName, JobTitle, City, StateProvinceName, CountryRegionName FROM HumanResources.vEmployee WHERE FirstName = @FirstNameVariable    OR PostalCode = @PostalCodeVariable; Hold out

A variant can also have a value assigned by being referenced in a pick out name. If a variable is referenced in a select inclination, it should be assigned a scalar value Oregon the SELECT statement should only return one row. For example:

            USE AdventureWorks2014; GO Adjudge @EmpIDVariable INT;  SELECT @EmpIDVariable = Liquid ecstasy(EmployeeID) FROM HumanResources.Employee; Blend

Warning

If there are sextuple assignment clauses in a single Choice statement, SQL Waiter does non guarantee the order of rating of the expressions. Federal Reserve note that effects are lonesome visible if there are references among the assignments.

If a Prime statement returns to a higher degree unmatched row and the protean references a non-scalar expression, the variable is go down to the value returned for the expression in the last row of the result set. E.g., in the following batch @EmpIDVariable is set to the BusinessEntityID value of the concluding row returned, which is 1:

            USE AdventureWorks2014; GO DECLARE @EmpIDVariable INT;  SELECT @EmpIDVariable = BusinessEntityID FROM HumanResources.Employee ORDER Away BusinessEntityID DESC;  SELECT @EmpIDVariable; GO

See Also

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