CODEWITHSUNDEEP
sqlsql-servert-sql
Leo Demarce
Leo Demarce

Reputation: 43

Using a variable as the column name

My question here is how do I use a variable to be a column name in a select statement. I have created the variable @B to be a column name that is BGNDATE1 through BGNDATE12. Rather than have 12 select statements I created a while loop. The column name is basically BGNDATE + the incremented integer.

The error I am getting is:

Conversion failed when converting the varchar value 'BGNDATE1' to data type int. 

USE X --this is the database    
DECLARE @DATES TABLE (ROWID INT, FISCDATES INT)

    DECLARE @FY INT =  2012
    DECLARE @I INT
    DECLARE @IV VARCHAR(2)
    DECLARE @B VARCHAR(9)

    SELECT @FY AS FY
    SET @I = 1
    WHILE @I <= 12
    BEGIN
    SET @IV = @I
    SET @B = 'BGNDATE' + @IV

    INSERT INTO @DATES (ROWID)
    SELECT @I

    MERGE INTO @DATES AS T
    USING (

--This is where the error is with regards to the variable @B

SELECT @B AS FISCDATES FROM DBO.Y  -- Y is the table in the database
  WHERE FSCYEAR = @FY) AS S
ON T.ROWID = @I
WHEN MATCHED 
    THEN UPDATE
    SET T.FISCDATES = S.FISCDATES;

SET @I = @I + 1
END

SELECT * FROM @DATES

Upvotes: 2

Views: 7898

Answers (2)

Guffa
Guffa

Reputation: 700182

You can't use a variable as a column name (unless you create the entire query dynamically), but you can use a variable to select from different columns:

...
SELECT
  CASE @IV
    WHEN 1 THEN BGNDATE1
    WHEN 2 THEN BGNDATE2
    WHEN 3 THEN BGNDATE3
    WHEN 4 THEN BGNDATE4
    WHEN 5 THEN BGNDATE5
    WHEN 6 THEN BGNDATE6
    WHEN 7 THEN BGNDATE7
    WHEN 8 THEN BGNDATE8
    WHEN 9 THEN BGNDATE9
    WHEN 10 THEN BGNDATE10
    WHEN 11 THEN BGNDATE11
    WHEN 12 THEN BGNDATE12
  END AS FISCDATES FROM DBO.Y
...

Upvotes: 2

Vulcronos
Vulcronos

Reputation: 3456

When you select @B that won't work because @B is not a column name, it is a variable.

It would be best to denormalize the table so instead of having 12 columns named BGNDATE 1 through 12 you had another table to join to.

If you can't do that, do it with dynamic sql:

exec('MERGE INTO @DATES AS T
    USING (    
SELECT ' + @B + ' AS FISCDATES FROM DBO.Y
  WHERE FSCYEAR = @FY) AS S
ON T.ROWID = @I
WHEN MATCHED 
    THEN UPDATE
    SET T.FISCDATES = S.FISCDATES;')

Upvotes: -1

Related Questions