CODEWITHSUNDEEP
sql-server
Sanya Zahid
Sanya Zahid

Reputation: 468

SQL query pass column name dynamically

This is my query I want to set column names dynamically, is it possible through query 

select  
    branch,
    sum(case when months = 'JUL' then  [Parts Revenue Budget] end) as [JUL]
from 
    #final 
group by 
    branch

Something like this:

@Col as varchar(10);

select  
    branch,
    sum(case when months = 'JUL' then  [@Col] end) as [JUL]
from 
    #final 
group by 
    branch

I get this below error message

Warning: Null value is eliminated by an aggregate or other SET operation.

Msg 207, Level 16, State 3, Procedure CRM_FRN_PL_BR_MN, Line 215
Invalid column name @Col

Upvotes: 0

Views: 7253

Answers (2)

Sergey Gornostaev
Sergey Gornostaev

Reputation: 7777

declare @col varchar(10);
declare @sql nvarchar(max);

set @col = 'some_field';
set @sql =  'select ' + @col + ' from #final';

exec sp_executesql @sql;

Upvotes: 2

FDavidov
FDavidov

Reputation: 3675

What you want to do is called dynamic SQL. It is supported by most DBMSs (you are not stating which one you are using though).

Normally, the process is to PREPARE a statement which is passed as a string, something like:

SER @Col_Name = 'Col_15' ;
SET @MySTTMNT = CONCAT('SELECT '           ,
                        @Col_Name     , ' ',
                       'FROM MyTable '     ,
                       'WHERE ID = '       ,
                       @MyVariable
                      ) ;

This string is then passed to a Prepare statement and then executed.

Check your DBMSs documentation for Dynamic SQL.

Upvotes: 2

Related Questions