Referring to column values directly without using variables in T-SQL

Question

Is there a way in T-SQL (SQL Server 2005) to assign a whole record to a record variable and then refer to the particular values using column names?

I mean, instead of:

select @var1 = col1,
       @var2 = col2
from mytable
where ID = 1;

and referring to them as @var1 and @var2, something like

@record = 
select col1, col2
from mytable
where ID = 1;

and referring to them like @record.col1 and @record.col2 .

I am beginner in t-sql, so hopefully the question is not too trivial.

Answer 1

You can create a table variable and select the whole resultset into it:

DECLARE  @tt TABLE (col1 INT, col2 INT)

INSERT
INTO    @tt
SELECT  col1, col2
FROM    mytable
WHERE   id = 1

, but you cannot access its data except than in the SELECT query as well.

With pure TSQL (that it without custom datatypes) the thing you ask is impossible.

Answer 2

Buried in the Transact SQL documentation I came across this restriction on variables:

Variables can be used only in expressions, not in place of object names or keywords.

Since you'd need to use an object name to qualify a column I don't believe that this is allowed.

Answer 3

sounds like you are a programmer ... look at linq maybe as it does what you want.

Answer 4

You can use a temporary table and SELECT...INTO to avoid specifying the column names at the beginning :

SELECT Field1, Field2
INTO #TempTable
FROM MyTable
WHERE MyTable.MyID = 1

but of course you'll still need the FROM #TempTable part when referring to the column names.

SELECT Field1, Field2
FROM #TempTable

and of course to remember to drop the table at the end :

DROP #TempTable

The app code is where you'd normally refer to a single row at a time as a variable.

Answer 5

You could use XML, but you'd have to play with this...

DECLARE @MyRecord xml
DECLARE @Mytable TABLE (col1 int NOT NULL, col2 varchar(30) NOT NULL)

INSERT @Mytable (col1, col2) VALUES (1, 'bob')

select @MyRecord =
    (SELECT *
    from @Mytable
    where col1 = 1
    FOR XML AUTO)

SELECT @myRecord.value('./@col', 'int') --also @myRecord.value('@col', 'int')

--gives error

Msg 2390, Level 16, State 1, Line 12
XQuery [value()]: Top-level attribute nodes are not supported