asked on

How to store Union output to a Variable

Storing UNION information to a table variable. I was able to create a union to determine the SalesRate for Texas, I want to store the information as a variable.
Union Query that run is results Tax Rate --7.50      StateProvince-- 73      CountryRegion --US
SELECT      TaxRate, StateProvinceID,CountryRegionCode
            FROM            AdventureWorks2016.Sales.SalesTaxRate a
            JOIN            AdventureWorks2016.Sales.SalesTerritory b
            ON                  a.ModifiedDate = b.ModifiedDate
            WHERE            a.[Name] lIKE '%Texas%'
            AND                  b.[Name] = 'NorthWest'

variable
DECLARE                  @TexasSalesTax      INT

                              SELECT            @TexasSalesTax = a.TaxRate
                              FROM            AdventureWorks2016.Sales.SalesTaxRate a
                              JOIN            AdventureWorks2016.Sales.SalesTerritory b
                              ON                  a.ModifiedDate = b.ModifiedDate
                              WHERE            a.[Name] lIKE '%Texas%'
                              AND                  b.[Name] = 'NorthWest'

                        SELECT * FROM @TexasSalesTax

How can I pull the information from the union table to variable?

Fred

ASKER

DECLARE                  @TexasSalesTax      INT

                              SET                        @TexasSalesTax =
                              (
                                    SELECT            TaxRate, StateProvinceID,CountryRegionCode
                                    FROM            AdventureWorks2016.Sales.SalesTaxRate a
                                    JOIN            AdventureWorks2016.Sales.SalesTerritory b
                                    ON                  a.ModifiedDate = b.ModifiedDate
                                    WHERE            a.[Name] lIKE '%Texas%'
                              AND                  b.[Name] = 'NorthWest'
                              )
                        SELECT * FROM @TexasSalesTax
                        Tried different approach - Error Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Dustin Saunders

Looks like you're getting started learning SQL, one note is that you're talking about a JOIN query. A UNION combines multiple result sets into one result set and is a different thing all together.

A variable can only hold one piece of information of its type. So if you want to get the province ID and country region code, you would need to declare 2 other variables and select into those.

Alternatively, you can declare a table as a variable and store the information that way. That looks like is

DECLARE @table TABLE (TaxRate INT, StateProvinceId INT, CountryRegionCode INT)

Open in new window

Then you can just INSERT your result to that table. It's sort of the 'object oriented' way of doing it in my mind.

For your questions, if you are specific about what you need to do with the results it can help get a more specific answer.

Fred

ASKER

Yes I am pretty new here with SQL, let me try and create a table variable that can retrieve Tax rate,state province and Country Region.

Fred

ASKER

The objective is to create a variable that stores the output similar to what the union did, that is to create a variable the pulls the TAXRATE and PROVINCEID from one table Sales.SalesTaxRate and use Sales.SalesTerritory to retrieve Texas, Which resides in NorthAmerica

ASKER CERTIFIED SOLUTION

Fred

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial