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

Question

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?

Looks dangerous: make sure you use a whitelist of column names to check against, do not just take user input — Charlieface
– Charlieface, Commented Oct 16, 2021 at 23:38

Aaron Bertrand · Accepted Answer · 2021-12-28 03:36:03Z

2

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;

Example db<>fiddle

Protecting yourself from SQL injection: Part 1 | Part 2

edited Dec 28, 2021 at 3:36

answered Oct 17, 2021 at 2:44

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

marc_s · Accepted Answer · 2021-10-17 06:12:45Z

1

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.

edited Oct 17, 2021 at 6:12

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Oct 16, 2021 at 22:48

ggordon

10.1k2 gold badges19 silver badges29 bronze badges

3 Comments

Mikko Over a year ago

Thank you @ggordon, this worked really well

Aaron Bertrand Over a year ago

@Mikko Note that this is easy, and works, but if that comma-separated list is ultimately coming from an end user, it is not safe.

DrPaulVella Over a year ago

could you use this to return aggregate results for the specified columns? for example count the number of NULL rows in each column?

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related