SQL Server Table Variable Example

By: Rick Dobson   |   Updated: 2019-05-20   |   Comments   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | > Temp Tables

Problem

A table variable is a local variable that has some similarities to temp tables.  Table variables are created via a declaration statement like other local variables.  Like other local variables, a table variable name begins with an @ sign.  However, its declaration statement has a type of table.  Within the defining declaration for a table variable, you can specify column names that have data types and constraints.  Table variables can be declared within batches, functions, and stored procedures, and table variables automatically go out of scope when the declaration batch, function, or stored procedure goes out of scope.  Within their scope, table variables can be used in SELECT, INSERT, UPDATE, and DELETE statements.  Unlike permanent and temp tables, table variables cannot be created and populated via the INTO clause in a SELECT statement.

Solution

For the examples below we are using the AdventureWorks2014 database. Download a copy and restore to your instance of SQL Server to test the below scripts.

Declaring, Populating, and Referencing a SQL Server Table Variable in a Batch

The following screen shot illustrates a lifecycle for a table variable within a T-SQL script.  The Messages tab below the script demonstrates how SQL Server interacts with the table variable during and after its lifecycle.  The term lifecycle denotes from when a table variable is declared until when the declaration goes out of scope.

• The declare statement at the top of the batch defines the @listOfAlphabetIDs table variable.
  • The table variable name begins with a mandatory @ sign.
  • The local variable type in the declaration statement is table.
  • The declaration specifies a column name (id) and a column data type of nvarchar with a maximum length of 1.
  • You can have specifications for more than one column, and you can have additional non-mandatory specifications, such as for column constraints, table constraints, and identity property columns.
• The INSERT statement following the declaration demonstrates one way to populate a declared table variable.  In this example, the @listOfAlphabetIDs table variable is assigned values of a and z on successive rows.  Consequently, the Messages tab shows two rows are affected by the statement.
• Next, a SELECT statement illustrates a simple way to list all the column values in a table variable.  Values are listed for the two populated rows so two rows are affected by this statement as well.
• The next SELECT statement has an outer-most query that references an inner query that references a derived table named my_derived_table.  The query referencing the derived table is discussed more fully in the tutorial section on derived tables.
• A where clause in the outer-most query sets a constraint on rows returned from derived table.
  • The constraint is that the first character of the name field from the derived table match one of the id column values in the  @listOfAlphabetIDs table variable.
  • For this demonstration, the outer-most query’s where clause subquery specifies the return of rows whose name column value begins with either a or z.  The outer-most query returns seventeen rows and the Messages tab confirms this outcome.
• The final SELECT statement in line number thirty fails.  The SELECT statement in line number 30 references the @listOfAlphabetIDs table variable.  However, the go keyword in line number 25 ends the batch with the declaration for the table variable.  Because the final SELECT statement that references the @listOfAlphabetIDs table variable runs in a different batch than the one with the declaration for the table variable, the final SELECT statement fails.  The error message in the Messages tab confirms the table variable is not declared in the batch running the statement.

The following screen shot displays the Results tab after running the code from the script in the preceding screen shot.

• The first pane shows the result set returned from the first SELECT statement.
• The second pane displays seventeen rows from the outer-most query in the second SELECT statement.
  • Fifteen rows are for countries whose name begins with a.
  • The final two rows are for countries whose name begins with z.

Insert for a Table Variable from a SQL Server Select Statement

The preceding example demonstrates how to populate a table variable sequentially with an INSERT statement and a series of values clause arguments.  The approach in the prior section is particularly appropriate when you wish to populate a table variable with set of ad hoc values.  However, many potential table variable applications will rely on subsets or transformed data that can be derived from existing database tables.  For this kind of application, it is best to follow an INSERT statement with a SELECT statement when inserting values into a table variable.  This section demonstrates the syntax for this approach.

The following script illustrates a three-step approach to defining and populating a table variable as well as displaying the values in a table variable.

• The first step appears in the first code block with a header comment of “declare table variable”.
  • The declare statement demonstrates the use of the primary key and not null column constraints.  The primary key constraint denotes a column with values that uniquely identify each row within a table variable.  The not null constraint means that missing values are not allowed in the column of a table variable.
  • This declare statement also includes a mix of different data types.  The prior sample included just one column with a nvarchar data type having a maximum length of one character.  The declaration in the following sample includes columns with int, nvarchar, and money data types.
• The second step in the code block is an INSERT statement that populates a table variable from the result set of a SELECT statement.
  • The design of the INSERT…SELECT statement illustrated below requires the SELECT list items be in the same order of the columns in the declare statement for the table variable.  If the SELECT list items are in a different order or are a subset of the columns specified in the declare statement, then specify the appropriate set of columns in parentheses after the INSERT statement.
  • The SELECT statement used below relies on data from the Product, ProductCategory, and ProductSubcategory tables in the AdventureWorks2014 database.
  • Inner joins are used to match rows from the tables among one another.
  • A where clause with an in operator extracts a subset of the rows from the result set computed up to this point in the query.  In the example below, only rows matching a ProductCategoryID value of 1 from the ProductCategory table are included in the final result set.
  • By changing the in operator arguments, you can require matches to different or more ProductCategoryID values.
• The third step is a SELECT statement with an asterisk to denote the return of all columns from the @product_table table variable.

-- declare table variable
DECLARE @product_table TABLE (
  ProductID int PRIMARY KEY NOT NULL,
  [Category Name] nvarchar(50) NOT NULL,
  [Subcategory Name] nvarchar(50) NOT NULL,
  [Product Name] nvarchar(50) NOT NULL,
  ListPrice money NOT NULL
);

-- bulk approach to populating a table variable 
INSERT @product_table
  SELECT
    [ProductID],
    sc.Name [Category Name],
    sc.Name [Subcategory Name],
    p.[Name] [Product Name],
    [ListPrice]
  FROM [AdventureWorks2014].[Production].[Product] p
  INNER JOIN [AdventureWorks2014].[Production].[ProductSubcategory] sc
     ON p.ProductSubcategoryID = sc.ProductSubcategoryID
  INNER JOIN [AdventureWorks2014].[Production].[ProductCategory] cat
     ON sc.[ProductCategoryID] = cat.[ProductCategoryID]
  WHERE cat.[ProductCategoryID] IN (1)
  ORDER BY cat.ProductCategoryID, sc.ProductSubcategoryID;

-- reference table variable
SELECT *
FROM @product_table;

Here’s an excerpt with the first twenty-five rows from the full set of ninety-seven rows returned by the query.  The excerpted rows are for Road Bikes and Mountain Bikes.  For the set of rows showing below, the one with the highest cardinality is Product Name.

Table Variables Can Be Returned From a SQL Server Function

While table variables can be created in stored procedures, their use is restricted to within the stored procedure declaring the variable.  This same kind of restriction applies to the batches within a T-SQL script; the initial script for table variables illustrates this limitation for referencing table variables in batches.  It is also possible to populate a table variable from within a user-defined function.  One special advantage of populating a table variable from within a function is that the table variable can be returned from the user-defined function for use outside the function.  The following script shows how you can implement this capability.

There are a couple of broad aspects for the solution below that may be worth reviewing before doing a step-by-step review of how the code works.  Each solution part is separated from the other by a line of dashes.  Recall that the first two dashes on any line define a comment line in a T-SQL script.

• A create function statement creates a function named ufn_SalesByStore.  The create function statement needs to be invoked just once in the database environment from which you run the solution.  The ufn_SalesByStore function computes the sum of sales by product for a store identified to the function as a passed parameter at run time.  This function returns a table variable named @My_TV with one row for each type of product (identified by ProductID and Name) sold at a store.
• A second part of the solution is based on a script.
  • This part of the solution successively invokes the ufn_SalesByStore function with different storeID parameters.
  • The script for invoking the ufn_SalesByStore function for different stores concatenates the table variable instances returned for different stores with union operators in the #output_from_multiple_table_variable_instances temp table.
  • Just before running the code to concatenate row sets for different products from different stores, the solution creates a fresh  copy of the #output_from_multiple_table_variable_instances temp table.

The code above the dashed line creates a fresh copy of the ufn_SalesByStore function.  This code needs to be run just once for any database context in which it runs.

• As its first step, this code block drops any previously existing function named ufn_SalesByStore.
• Next, a create function statement initiates the generation of a new version of the ufn_SalesByStore function.  The last part of this statement includes the @storeID parameter designation which specifies the store for which the function is to compute the total sales by product for a table variable.  Each row of the computed table variable is for a different type of product sold at a store.  All the rows in a table variable instance are for a specific store.  The @storeID parameter designates which store.
• On each invocation of the function, a fresh copy of the table variable named @My_TV is declared in the returns clause of the user-defined function.  The table variable has columns for store and product identifiers along with a field called Total defined with the money data type.  The Total column is for the sum of the sales for a product type at a store.

• Within the user-defined function, line-item totals from the SalesOrderDetail table of the AdventureWorks2014 database are summed.  The sums are for product identifiers and for a store identifier; the store identifier is passed as a parameter to the function.  Therefore, the table variable has a separate row for each product sold at a store.
• A SELECT statement following the INSERT into statement invokes a sum function to aggregate the line item Total column values.  The SELECT statement groups the rows by ProductID and Name and filters rows to include just those for the @storeID parameter value passed to the function.

The code below the dashed line starts by creating a fresh copy of the  #output_from_multiple_table_variable_instances temp table.  This table stores the concatenated instances of the @My_TV table variables for different @storeID values.

The SELECT statement after the code to create a fresh copy of the #output_from_multiple_table_variable_instances temp table invokes the ufn_SalesByStore function for a set of stores.  Here’s the logic for how this code operates.

• The inner part of the SELECT statement contains a subquery named storesIDs_with_total_by_product_ID.  This subquery is just a derived table.
• In the example, the subquery invokes the ufn_SalesByStore function once for each of four different @storeID values.  The @storeID values are 292, 294, 296, and 298.
  • Each time the ufn_SalesByStore function operates for a @storeID value, it generates an instance of the @My_TV table variable for a store.
  • The union operators concatenate the @My_TV instances for each store into an integrated result set across all four stores.
• In the outer query, an into clause populates the #output_from_multiple_table_variable_instances temp table with the integrated result set.

The final SELECT statement in the script displays the contents of the #output_from_multiple_table_variable_instances temp table.

-- conditionally drop function
IF EXISTS (SELECT *
           FROM sys.objects
           WHERE object_id = OBJECT_ID(N'[dbo].[ufn_SalesByStore]')
             AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT')
          )
   DROP FUNCTION [dbo].[ufn_SalesByStore]
GO

-- function returns sales by product in a table variable for a store
CREATE FUNCTION [dbo].[ufn_SalesByStore] (@storeid int)
RETURNS @My_TV TABLE (
  storeID int,
  ProductID int,
  Name nvarchar(50),
  Total money
)
AS
BEGIN
  -- sums LineTotal across all line items for all orders at a store
  -- StoreID is in Customer table
  INSERT INTO @My_TV
    SELECT
      @storeid [StoreID],
      P.ProductID,
      P.Name,
      SUM(SD.LineTotal) [Total]
    FROM AdventureWorks2014.Production.Product AS P
    JOIN AdventureWorks2014.Sales.SalesOrderDetail AS SD
      ON SD.ProductID = P.ProductID
    JOIN AdventureWorks2014.Sales.SalesOrderHeader AS SH
      ON SH.SalesOrderID = SD.SalesOrderID
    JOIN AdventureWorks2014.Sales.Customer AS C
      ON SH.CustomerID = C.CustomerID
    WHERE C.StoreID = @storeid
    GROUP BY P.ProductID,
             P.Name
  RETURN
END
GO

----------------------------------------------------------------------------

IF OBJECT_ID('tempdb..#output_from_multiple_table_variable_instances') IS NOT NULL
  DROP TABLE #output_from_multiple_table_variable_instances

-- invoke user-defined function that returns a table-value object from a table 
SELECT * 
INTO #output_from_multiple_table_variable_instances
FROM (SELECT * FROM dbo.ufn_SalesByStore(292)
      UNION
      SELECT * FROM dbo.ufn_SalesByStore(294)
      UNION
      SELECT * FROM dbo.ufn_SalesByStore(296)
      UNION
      SELECT * FROM dbo.ufn_SalesByStore(298)
     ) storesIDs_with_total_by_product_ID


SELECT * FROM #output_from_multiple_table_variable_instances

To help you follow the description of the preceding code block, the next four screen shots present the first ten rows for stores 292, 294, and 296 followed by the last ten rows for store 298.

• Each of the four screen shots below present a snapshot of the concatenated result set for a different store.
• The first three screen shots are for the initial ten rows in the @My_TV table variable instance for stores 292, 294, and 296.
• While there is a substantial amount of similarity in the rows for the first three stores, there are some differences as well.  The important point to grasp is that each snapshot is for the sales at a store by product name.
• As you can see from the last of the four screen shots, there are 226 rows across the concatenated result sets.

Next Steps

Here are some links to resources that you may find useful to help you grow your understanding of content from this section of the tutorial.

• SQL Server Table Variables
• table (Transact-SQL)
• SELECT INTO a table variable in T-SQL
• Comparative Analysis of Table Variables on User Defined Functions, Indexes and Scope
• Declare variable in table valued function
• Drop Function if exist – SQL Server
• GO After every T-SQL statement

About the author

Rick Dobson is an author and an individual trader. He is also a SQL Server professional with decades of T-SQL experience that includes authoring books, running a national seminar practice, working for businesses on finance and healthcare development projects, and serving as a regular contributor to MSSQLTips.com. He has been growing his Python skills for more than the past half decade -- especially for data visualization and ETL tasks with JSON and CSV files. His most recent professional passions include financial time series data and analyses, AI models, and statistics. He believes the proper application of these skills can help traders and investors to make more profitable decisions.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips