1

I might be asking the same question as this, but perhaps if I ask it my way, a different solution may become obvious. I have this:

DECLARE @Table1 AS TABLE(
Table1ID Int Primary Key
,ColumnName varchar(max)
)
INSERT INTO @Table1(Table1ID,ColumnName) VALUES(1000,'A')
INSERT INTO @Table1(Table1ID,ColumnName) VALUES(2000,'B')
DECLARE @Table2 AS TABLE(
Table2ID Int Identity Primary Key
,Table1ID Int
,FieldData varchar(max)
)
INSERT INTO @Table2(Table1ID,FieldData) VALUES(1000,'A DATA')
INSERT INTO @Table2(Table1ID,FieldData) VALUES(2000,'B DATA')
SELECT * FROM @Table1
SELECT * FROM @Table2
SELECT FieldData AS A 
from @Table2
WHERE Table1ID = 1000
SELECT FieldData AS B
from @Table2
WHERE Table1ID = 2000

And I want to do this:

SELECT FieldData AS [Table1.ColumnName]
from @Table2
WHERE Table1ID = 2000
2
  • 2
    Do you need to use table variables? Your life would be a lot simpler with #temp tables. – Aaron Bertrand Apr 23 '15 at 20:07
  • You really, really don't want to put a dot in the alias name. Even though [Table1.ColumnName] is in square brackets and works, all sorts of other things in the SQL Server stack will break. – Michael Green Apr 24 '15 at 2:37
3

If you can use #temp tables instead of table variables, this becomes dead easy.

CREATE TABLE #Table1
(
 Table1ID Int Primary Key,
 ColumnName varchar(max)
);

INSERT INTO #Table1(Table1ID,ColumnName) 
  VALUES(1000,'A'),(2000,'B');

CREATE TABLE #Table2
(
  Table2ID Int Identity Primary Key,
  Table1ID Int,
  FieldData varchar(max)
);

INSERT INTO #Table2(Table1ID,FieldData) 
  VALUES(1000,'A DATA'),(2000,'B DATA');

Now, I can do this:

DECLARE @sql NVARCHAR(MAX), @Table1ID INT = 2000;

SELECT @sql = N'SELECT FieldData AS ' + QUOTENAME(ColumnName) 
  + N' FROM #Table2 WHERE Table1ID = @Table1ID;'
FROM #Table1 WHERE Table1ID = @Table1ID;

EXEC sys.sp_executesql @sql, N'@Table1ID INT', @Table1ID; 

Results:

B
------
B DATA

If you need to use table variables for some reason (as explained in an excellent answer by Martin Smith, they're actually usually less desirable than #temp tables, or indifferent even in ways where most people make assumptions that table variables are better), then you can use a really fugly hack based on this answer (also by Martin Smith, coincidentally).

First, you need to create a table type in your database (and creating a copy of this in model may be a good idea as well, because in some scenarios it may need to also exist in tempdb).

CREATE TYPE dbo.Table2 AS TABLE
(
  Table2ID Int Identity Primary Key,
  Table1ID Int,
  FieldData varchar(max)
);

Now, your code would be:

DECLARE @Table1 AS TABLE
(
  Table1ID Int Primary Key,
  ColumnName varchar(max)
);

DECLARE @Table2 AS dbo.Table2;

INSERT INTO @Table1(Table1ID,ColumnName)
 VALUES(1000,'A'),(2000,'B');
INSERT INTO @Table2(Table1ID,FieldData)
 VALUES(1000,'A DATA'),(2000,'B DATA');

DECLARE @sql NVARCHAR(MAX), @Table1ID INT = 2000;

SELECT @sql = N'SELECT FieldData AS ' + QUOTENAME(ColumnName)
  + N' FROM @Table2 WHERE Table1ID = @Table1ID;'
FROM @Table1 WHERE Table1ID = @Table1ID;

EXEC sys.sp_executesql @sql, 
  N'@Table2 dbo.Table2 READONLY, @Table1ID INT',
  @Table2 = @Table2, @Table1ID = @Table1ID;

Results, again:

B
------
B DATA

From a simplicity (and portability / forward compatibility) perspective, I would argue for the former solution where you just use #temp tables instead of table variables and a TVP.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.