How to store multiple values in TSQL Variables

Question

I have the following TSQL Statement:

SELECT ProductId, OwnerId FROM Product

How I can store the ProductId and OwnerId in a TSQL variable and then return it to the calling application?

Answer 1

This sort of thing is usually done by executing the query and evaluating the results in the application using whatever framework is appropriate (for example, in .NET you'd either use one of the many ORM tools, the ADO.NET objects like DataTable, or a DbDataReader), rather than by returning them as a variable. For one thing, returning the values as a variable will mean that you'll only get the values from one row, regardless of how many are in your result set.

If this really is what you want (and need) to do, then you'll have to figure out some way to combine them into one value. Usually something like using a vertical bar (|) and separating the string values then splitting it back up on the client side works.

Answer 2

You cannot store it into a single variable - one variable can hold one value.

But you can easily store it into a in-memory table - something like:

CREATE TABLE @Temporary(ProductID INT, OwnerID INT)

INSERT INTO @Temporary(ProductID INT, OwnerID INT)
  SELECT ProductId, OwnerId 
  FROM dbo.Product

With this, you now have a table in memory, which you can use like any other table - select from it, join it to on-disk tables - whatever you need to do. You can return this to a calling application as a result set - like any other query result.

Answer 3

In general, no you cannot store multiple values in a variable without some hoop-jumping like delimited lists. In your example, presuming that the Product table has a single row, you can do something like:

Declare @ProductId <datatype>
Declare @OwnerId <datatype>

Select @ProductId = ProductId
    , @OwnerId = OwnerId
From Product

If the Product table has more than one row, this will not work and what you need to do next depends greatly on what you are actually trying to accomplish.

Answer 4

If you're absolutely set on loading those values into a variable:

DECLARE @foo  varchar(256)

 SET ROWCOUNT 1;
 SELECT @foo = 
        CAST(ProductId AS varchar(100)) + '|' +  CAST(OwnerId AS varchar(100)
 FROM   Product
 WHERE  ProductName = 'foo'