Providing string variable into Dynamic SQL SELECT statement WITHOUT it being read as a column name?

Question

I have a situation where I am trying to create a query using Dynamic SQL in SSMS where I am trying to generate a complete file path by concatenating both the name of a parent folder(s) (which change on each project) and a filename from a table (the name of this table changes on each project, as well). I can successfully pass the name of the table to my Dynamic SQL in a variable, but I'm struggling with the file path inside of the SELECT CONCAT() statement, because SSMS keeps trying to read this value as a column name for that table, rather than as a string.

Here's some sample data:

SELECT * INTO AAA_Demo_Table FROM (VALUES ('Image01.png'), ('Image02.png'), ('Report00001.jpg'), ('Image01.jpg'), ('File1.gif')) AS [Filename] ([Filename])

This first query works, though it obviously doesn't take advantage of the variables that I've established:

DECLARE @DemoTable VARCHAR(200)
DECLARE @Folder01 VARCHAR(200)
DECLARE @Folder02 VARCHAR(200)
DECLARE @Query1 NVARCHAR(MAX)

SET @DemoTable = 'AAA_Demo_Table' -- Replace with the name of your project
SET @Folder01 = 'Documents'
SET @Folder02 = 'EMD_Images'

-- THIS EXAMPLE WORKS
SET @Query1 = '
SELECT CONCAT(''Documents'', ''/'', ''Images'', ''/'', FileName) AS FilePaths
FROM AAA_Demo_Table
'

EXEC sp_executesql @Query1

Next, if I try sliding my table name in dynamically, that also works:

DECLARE @DemoTable VARCHAR(200)
DECLARE @Folder01 VARCHAR(200)
DECLARE @Folder02 VARCHAR(200)
DECLARE @Query1 NVARCHAR(MAX)

SET @DemoTable = 'AAA_Demo_Table' -- Replace with the name of your project
SET @Folder01 = 'Documents'
SET @Folder02 = 'Images'

-- THIS EXAMPLE WORKS
SET @Query1 = '
SELECT CONCAT(''Documents'', ''/'', ''Images'', ''/'', ADT.FileName) AS FilePaths
FROM ' + @DemoTable + ' ADT
'

EXEC sp_executesql @Query1

But when I try to dynamically provide the file names into the Dynamic SQL, it fails:

DECLARE @DemoTable VARCHAR(200)
DECLARE @Folder01 VARCHAR(200)
DECLARE @Folder02 VARCHAR(200)
DECLARE @Query1 NVARCHAR(MAX)

SET @DemoTable = 'AAA_Demo_Table' -- Replace with the name of your project
SET @Folder01 = 'Documents'
SET @Folder02 = 'Images'

-- THIS EXAMPLE FAILS
SET @Query1 = '
SELECT CONCAT(' + @Folder01 + ', ''/'', ' + @Folder02 + ', ''/'', ADT.FileName) AS FilePaths
FROM ' + @DemoTable + ' ADT
'

EXEC sp_executesql @Query1

I've tried searching quite a bit for an answer here, but I haven't had any luck. All of the examples that I can find are either not using variables inside the SELECT statement (they're providing a variable to FROM or WHERE, usually), or they are setting the entire SELECT statement into a variable, rather than storing a string to be placed into a subsequent SELECT statement.

In my case, this is a much smaller portion of a larger query where I want to do a lot of handling of these filenames, and the top level folders would be referenced multiple times. Saving the entire SELECT statement into a single variable would be functionally indistinguishable from just having the user scroll through the query and make all of the replacements, which is why I was hoping to use this approach of only having the user provide the table name and top-level folders once and then applying that to the later queries.

Answer 1

Just pass your parameters as parameters, don't inject them. Also sanitise your object names. What you had was wide open to SQL Injection attacks.

DECLARE @DemoTable sysname; --corrected datatype
DECLARE @Folder01 VARCHAR(200);
DECLARE @Folder02 VARCHAR(200);
DECLARE @Query1 NVARCHAR(MAX);

SET @DemoTable = N'AAA_Demo_Table' -- Replace with the name of your project
SET @Folder01 = 'Documents';
SET @Folder02 = 'Images';

-- THIS EXAMPLE FAILS
SET @Query1 = '
SELECT CONCAT(@Folder01, ''/'', @Folder02, ''/'', ADT.FileName) AS FilePaths
FROM dbo.' + QUOTENAME(DemoTable) + ' ADT;';

EXEC sys.sp_executesql @Query1, N'@Folder01 varchar(200), @Folder02(200)', @Folder01, @Folder02;