Need help variable substitution in SQL

Question

Hi, Is there a way to pass the value of combination variable to another variable? I guess better of with a simple code that I wrote...

DECLARE @intFlag INT  
DECLARE  @taxdepn1 varchar(1) = 'A' 
    ,@taxdepn2 varchar(1) = 'B' 
    ,@taxdepn3 varchar(1) = 'C' 
    ,@taxdepn4 varchar(1) = null --'D' `
DECLARE @xxx varchar(1000);
SET @intFlag = 1
WHILE (@intFlag <=4)
BEGIN
    set @xxx = '@taxdepn'+cast(@intFlag as CHAR) ; 
    -- Here, I want to get the actual value of @Taxdepn1 to @TaxDepn4
    if @xxx is not null  
        begin
            print 'do something for '+@xxx
    end 
    set @intFlag = @intFlag+1 ;
End

Expected output  
    do something for A  
    do something for B  
    do something for C  

I really appreciate any help.

Thanks.
Elmer

Answer 1

In order to do what you're attempting, you'd need to use dynamic SQL. Here's an alternative that doesn't require dynamic SQL, but that you statically define the reference you want to make:

WHILE (@intFlag <=4)
BEGIN    

    SET @xxx = CASE @intFlag
                        WHEN 1 THEN @taxdepn1
                        WHEN 2 THEN @taxdepn2
                        WHEN 3 THEN @taxdepn3
                        ELSE @taxdepn4
                     END

    IF @xxx IS NOT NULL 
    BEGIN
      PRINT @xxx
    END

END 

Answer 2

Using sp_executesql, but is rather cumbersome. The @taxdepn1...@taxdepn4 must be passed in as arguments and the dynamic sql can then use them to do an on-the-fly evaluation, returning the value assigned to an output parameter:

while (@intFlag <= 4)
begin
  declare @sql nvarchar(max);
  set @sql = N'set @out = @in' + cast(@intFlag as nchar);
  declare @xxx char(1);
  exec sp_executesql @sql, 
      N'@in1 char(1), @in2 char(1), @in3 char(1), @in4 char(1), @out char(1) output',
      @taxdepn1, @taxdepn2, @taxdepn3, @taxdepn4, @xxx output;
  if @xxx is not null
  begin
    ...
  end
  set @intFlag += 1;
end