SQL Server : sending table ColumnName as Dynamic sql Argument

monjurit · March 14, 2016, 8:02pm

I have to write a procedure where I have to pass column name as a passing argument. The column name will be created from a select statement using some case conditions. The problem is that when I put the dynamically generated column name in the select statement like this

Select @columnName 
from TableName

It does not pull the data of the column from the table. Here is the full code

declare @term as varchar(10) ='201610'
declare @ColumnName CHAR(10)
declare @Source nvarchar (4000)
set @Source =N'
declare @ColumnName VARCHAR(10)
DECLARE @replacement TABLE (ratio varchar(50), CCode varchar(10), t1 float, t2 float, t3 float, t4 float, t5 float)

insert into @replacement

select        case    when Level=''A'' then ''0''
                    when Level=''B'' then ''1''
                    when Level in (''C'', ''D'') then ''2''    
            end as ratio, 
            new.CCode, 
            1.0*sum(new.t1)/sum(deg.t1) as t1,
            1.0*sum(new.t2)/sum(deg.t2) as t2,
            1.0*sum(new.t3)/sum(deg.t3) as t3,
            1.0*sum(new.t4)/sum(deg.t4) as t4,
            1.0*sum(new.t5)/sum(deg.t5) as t5
from
            (
                select    level, 
                        CCode, 
                        sum(case when (Term/100)='+ @Term +'/100-4 then val end) as t1,
                        sum(case when (Term/100)='+ @Term +'/100-3 then val end) as t2,
                        sum(case when (Term/100)='+ @Term +'/100-2 then val end) as t3,
                        sum(case when (Term/100)='+ @Term +'/100-1 then val end) as t4,
                        sum(case when (Term/100)='+ @Term +'/100 then val end) as t5
                from    new
                group by level, CCode
            )new
left join
            (
                select    levelCode, CCode, 
                        sum(case when (Term/100)='+ @Term +'/100-4 then val end) as t1,
                        sum(case when (Term/100)='+ @Term +'/100-3 then val end) as t2,
                        sum(case when (Term/100)='+ @Term +'/100-2 then val end) as t3,
                        sum(case when (Term/100)='+ @Term +'/100-1 then val end) as t4,
                        sum(case when (Term/100)='+ @Term +'/100 then val end) as t5
                from    deg
                group by levelCode, CCode
            )deg
            on new.level=deg.levelCode and new.CCode=deg.CCode

group by 
            case    when Level=''A'' then ''0''
                    when Level=''B'' then ''1''
                    when Level in (''C'', ''D'') then ''2''    
            end, 
            new.CCode
                

set @ColumnName= (Select top 1 Column1 from 
(Select Case when t5 is not null then ''t5''
            when t5 is null and t4 is not null then ''t4''
            when t5 is null and t4 is null and t3 is not null then ''t3''
            when t5 is null and t4 is null and t3 is null and t2 is not null then ''t2''
            end as Column1 
from @replacement
)tempt    
where column1 is not null)
--Select @ColumnName

Select t4 from @replacement
Select  @ColumnName from @replacement
Select ' + @ColumnName + ' from @replacement
'    

EXEC(@Source)

For the select statement Select t4 from @replacement it prints the column data. For the statement
Select @ColumnName from @replacement it just prints the column name stored in @ColumnName.
For the statement Select ' + @ColumnName + ' from @replacement, it does not print any table. The result window shows just one message: Command(s) completed successfully.

Could anyone tell me what is wrong with the code? Is there any other
way to get the data of the column whose name is dynamically pulled like
the code above?
I am running the query in Microsoft SQL Server Management Studio 11.0.2100.60.

Thank you.

TaraKizer · March 14, 2016, 8:21pm

It doesn't appear you need dynamic SQL until the final SELECT statement. Move everything but the final SELECT outside of it and then just this inside: SET @Source = 'SELECT ' + @ColumnName + ' FROM @replacement'. Then do the EXEC. Everything else should not be inside dynamic SQL.

monjurit · March 14, 2016, 8:38pm

Thanks a lot for your reply. I tried doing that. Since I am using table variable, the dynamic SQL cannot recognize the table @replacement which is created outside of it. I have to use table variable for fast execution of the query over large data set. Here is my modified code.

declare @term as int=201610
declare @ColumnName CHAR(10)
declare @Source nvarchar (4000)
DECLARE @replacement TABLE (ratio varchar(50), CCode varchar(10), t1 float, t2 float, t3 float, t4 float, t5 float)

insert into @replacement

select        case    when Level='A' then '0'
                    when Level='B' then '1'
                    when Level in ('C', 'D') then '2'    
            end as ratio, 
            new.CCode, 
            1.0*sum(new.t1)/sum(deg.t1) as t1,
            1.0*sum(new.t2)/sum(deg.t2) as t2,
            1.0*sum(new.t3)/sum(deg.t3) as t3,
            1.0*sum(new.t4)/sum(deg.t4) as t4,
            1.0*sum(new.t5)/sum(deg.t5) as t5
from
            (
                select    level, 
                        CCode, 
                        sum(case when (Term/100)=@Term/100-4 then val end) as t1,
                        sum(case when (Term/100)=@Term/100-3 then val end) as t2,
                        sum(case when (Term/100)=@Term/100-2 then val end) as t3,
                        sum(case when (Term/100)=@Term/100-1 then val end) as t4,
                        sum(case when (Term/100)=@Term/100 then val end) as t5
                from    new
                group by level, CCode
            )new
left join
            (
                select    levelCode, CCode, 
                        sum(case when (Term/100)=@Term/100-4 then val end) as t1,
                        sum(case when (Term/100)=@Term/100-3 then val end) as t2,
                        sum(case when (Term/100)=@Term/100-2 then val end) as t3,
                        sum(case when (Term/100)=@Term/100-1 then val end) as t4,
                        sum(case when (Term/100)=@Term/100 then val end) as t5
                from    deg
                group by levelCode, CCode
            )deg
            on new.level=deg.levelCode and new.CCode=deg.CCode

group by 
            case    when Level='A' then '0'
                    when Level='B' then '1'
                    when Level in ('C', 'D') then '2'    
            end, 
            new.CCode
                

set @ColumnName= (Select top 1 Column1 from 
(Select Case when t5 is not null then 't5'
            when t5 is null and t4 is not null then 't4'
            when t5 is null and t4 is null and t3 is not null then 't3'
            when t5 is null and t4 is null and t3 is null and t2 is not null then 't2'
            end as Column1 
from @replacement
)tempt    
where column1 is not null)
--Select @ColumnName

set @Source =N'Select ' + @ColumnName + ' from @replacement'    

EXEC(@Source)

It shows the following error message

(28 row(s) affected)
Msg 1087, Level 15, State 2, Line 2
Must declare the table variable "@replacement".

TaraKizer · March 14, 2016, 8:56pm

Oops! Yeah the table variable isn't in the same session as the dynamic SQL query. Is there a chance that more than one user will run it at the same time? If not, you can use a global temp table, like ##replacement, which is available to all sessions.

monjurit · March 14, 2016, 9:07pm

Now with global temp table the query is working.

Thanks a lot for your help.

TaraKizer · March 14, 2016, 9:24pm

Whoop!

Kristen · March 15, 2016, 7:15am

I think?? an ordinary #TEMP table will be in scope - which would avoid the possibility that someone else was using the same global-named **##**TEMP table

You might like to read up about SQL Injection when using dynamic SQL like this. Basically if you gave the user a data entry form and let them type the column name into it and they typed

1234;DROP DATABASE SomeName;--

that would cause the, valid dynamic SQL to be executed:

Select 1234;
DROP DATABASE SomeName;
--  from @replacement

In this instance probably the easiest way around this would be to use

set 'Select ' + QuoteName(@ColumnName) + ' from @replacement'

which will also take care of the situation where @ColumnName includes spaces or other characters that require the column name to be quoted as [MyColumnName]

You also need to be careful of any concatenated strings that include the single quote character. Don't think it matters in this case, but it would do if your dynamic SQL, itself, includes a concatenation of EXEC and some string variable.