SQL Server 2017 - How to pass a parameter in a SELECT inside a dynamic SQL

Question

I've created the following table:

CREATE TABLE TestABC (
  colID INT NOT NULL,
  colName VARCHAR(10) NULL,
  );

INSERT INTO TestABC 
    (colID, colName) 
VALUES 
    (1,'ABC'),
    (2,'DEF'),
    (3,'GHI');

When I run the following query against the above table...

DECLARE @Name varchar(max)
select @Name = colName from dbo.TestABC where colID = '3'
print @Name

I get the following expected result, which is good:

GHI

Now, I am trying to pass the SELECT with my @Name parameter inside a dynamic SQL:

DECLARE @sql varchar(max)
DECLARE @Name varchar(max)
set @sql = '
select ' + @Name + ' = colName from dbo.TestABC where colID = ''3''
'
print @sql

The problem I am having is that, inside the dynamic SQL, my @Name parameter does not get evaluated and results in a blank or NULL. Also, my entire dynamic sql parameter @sql also ends up NULL.

I fully realize that, if I add the following additional line before my dynamic SQL, that the @Name should be evaluated properly...

SELECT @Name = colName from dbo.TestABC

However, that is not my aim. I need the SELECT to be completely dynamic since I will be passing my dbo.TestABC dynamically eventually. Therefore, I cannot have my @Name parameter pre-assigned as in the above SELECT.

How do I populate my @Name and @sql parameters in my SELECT inside my dynamic SQL?

Answer 1

when you do:

set @sql = '
select ' + @Name + ' = colName from dbo.TestABC where colID = ''3''
'

the actual value inside variable @Name is null. And according to sql server logic string + null is null.

to do what you want you need:

DECLARE @sql nvarchar(max)
DECLARE @Name nvarchar(max)
set @sql = '
select @someVariable = colName from dbo.TestABC where colID = ''3''
'
EXECUTE sp_executesql @sql, N'@someVariable varchar(max) OUTPUT', @someVariable = @Name OUTPUT;
select @Name; -- this will print your colName

and do not use (max) as your default its a bad practice and might slow your queries down.