Say I have the table Depots below

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

SELECT @ValList returns DepotID, DepotName, DepLocation

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

SELECT @ValList FROM  Depots

So that I get

But I keep getting something like

What am I doing wrong?

CodePudding user response：

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;

View working demo

Let me know if this works for you.

CodePudding user response：

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