CODEWITHSUNDEEP
sqlsql-servervariablesdynamic-sql
Mikko
Mikko

Reputation: 29

How to Pass columns contained in a declared variable in SQL Server

I have a table Depots that looks like this:

DepotID DepotName DepotLocation DepAlias
1 Ouland Utsacity Oula
2 Ingri Utsacity Inglas
3 Turks Utsacity Turku
4 tamps Uusimaa Tampere
5 Kokos Uusimaa Kokoola
6 Kaus Olomba Kaukana

I stored a comma-separated list of columns in a declared variable @ValList

DECLARE @ValList varchar(8000);
SET @ValList = NULL

SELECT @ValList = COALESCE(@ValList + ', ','') + ColumnName 
FROM #list

@ValList returns DepotID, DepotName, DepLocation

I want to pass @ValList into a select statement like below

SELECT @ValList FROM Depots

So that I get

DepotID DepotName DepotLocation
1 Ouland Utsacity
2 Ingri Utsacity
3 Turks Utsacity
4 tamps Uusimaa
5 Kokos Uusimaa
6 Kaus Olomba

But I keep getting something like

(No column name)
DepotID, DepotName, DepLocation
DepotID, DepotName, DepLocation
DepotID, DepotName, DepLocation
DepotID, DepotName, DepLocation
DepotID, DepotName, DepLocation
DepotID, DepotName, DepLocation

What am I doing wrong?

Upvotes: 0

Views: 912

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280431

Not sure how much you can or should trust the source of the list of columns, but the safest way to do this is as follows, where you check the existence of each column in sys.columns:

CREATE TABLE #list(ColumnName sysname);

INSERT #list(ColumnName) 
VALUES(N'DepotID'),(N'DepotName'),(N'DepLocation');

DECLARE @sql nvarchar(max), @cols nvarchar(max) = N'';

SELECT @cols += N',' + QUOTENAME(c.name) 
  FROM #List AS l
  INNER JOIN sys.columns AS c
  ON l.ColumnName = c.name
  WHERE c.[object_id] = OBJECT_ID(N'dbo.Depots');
  
SET @sql = N'SELECT ' + STUFF(@cols, 1, 1, N'') 
  + N' FROM dbo.Depots';
  
EXEC sys.sp_executesql @sql;

Protecting yourself from SQL injection: Part 1 | Part 2

Upvotes: 2

ggordon
ggordon

Reputation: 10035

You seem interested in dynamic TSQL. Try using sp_executesql Eg.

DECLARE @ValList varchar(8000);
SET @ValList = 'DepotID, DepotName, DepotLocation';

SELECT @ValList as ColumnNames;

DECLARE @MyQuery NVARCHAR(4000) = CONCAT(N'SELECT ',@ValList,N' FROM  Depots');

EXECUTE sp_executesql @MyQuery;
ColumnNames
DepotID, DepotName, DepotLocation
DepotID DepotName DepotLocation
1 Ouland Utsacity
2 Ingri Utsacity
3 Turks Utsacity
4 tamps Uusimaa
5 Kokos Uusimaa
6 Kaus Olomba

View working demo

Let me know if this works for you.

Upvotes: 1

Related Questions