CODEWITHSUNDEEP
sqlsql-server
doron almog
doron almog

Reputation: 21

How can I leverage a variable as a column name

I am getting the following error when I try to use a variable as a column name.

Error

Msg 207, Level 16, State 1, Line 45
Invalid column name '@WKNUM'.

Code

SET NOCOUNT ON
GO

DECLARE @WKNUM int

SET @WKNUM = 17
WHILE (@WKNUM < 20)
BEGIN;

    UPDATE dbo.DataModel_TEST_NUM
    SET dbo.DataModel_TEST_NUM.[@WKNUM] = dbo.TEST2.[New Value]
    FROM dbo.TEST2
    INNER JOIN dbo.DataModel_TEST_NUM
        ON dbo.TEST2.[Opportunity ID] = dbo.DataModel_TEST_NUM.opportunityID
    WHERE (dbo.TEST2.WKNUM = @WKNUM)

    SET @WKNUM = @WKNUM + 1
END;
GO
SET NOCOUNT OFF
GO

Any idea why it is giving this error?

Upvotes: 2

Views: 67

Answers (3)

Ajay2707
Ajay2707

Reputation: 5808

There is only one issue I found that you used variable name instead of actual column name.

begin  tran
create table DataModel_TEST_NUM (opportunityID int, WKNUM int )
create table TEST2 (opportunityID int, WKNUM int, new_value int )

insert into DataModel_TEST_NUM values (1, 18) , (2,25 ),(3,19 ), (4,30)

insert into TEST2 values (1, 18, 19) , (2 ,25 ,26 ),(3,19, 20 ), (4,30,31)


--SET NOCOUNT ON
--GO
select * from DataModel_TEST_NUM

DECLARE @WKNUM int

SET @WKNUM = 17
WHILE (@WKNUM < 20)
BEGIN;

    UPDATE dbo.DataModel_TEST_NUM
    SET [WKNUM] = dbo.TEST2.[new_value]
    --select *
    FROM dbo.TEST2
    INNER JOIN dbo.DataModel_TEST_NUM
        ON dbo.TEST2.[opportunityID] = dbo.DataModel_TEST_NUM.opportunityID
    WHERE (dbo.TEST2.WKNUM = @WKNUM)

    SET @WKNUM = @WKNUM + 1
END;
--GO
--SET NOCOUNT OFF
--GO

select * from DataModel_TEST_NUM
select * from test2

rollback 

--actual data
opportunityID   WKNUM
    1           18
    2           25
    3           19
    4           30

--after while loop and update query 
opportunityID   WKNUM
    1           19
    2           25
    3           20
    4           30

I have some suggestion for you.

  1. While testing SET NOCOUNT option is always off.
  2. Alywas follow the standarad rule when you create a table, in your case you give space in column name. Never create this type of column-name.

Upvotes: 0

Dhaval
Dhaval

Reputation: 2379

UPDATE dbo.DataModel_TEST_NUM
SET    dbo.DataModel_TEST_NUM.[@WKNUM] = dbo.TEST2.[New Value]------What you are try to do here ...!!!
 ---you can not define column name like this..
FROM   dbo.TEST2
INNER JOIN
dbo.DataModel_TEST_NUM ON dbo.TEST2.[Opportunity ID] = dbo.DataModel_TEST_NUM.opportunityID
WHERE        (dbo.TEST2.WKNUM = @WKNUM )

you define @WKNUM as INT that will give you value 17,18,19.. is it confirm that you have column name like this...if it is then you have to use dynamic sql like this..

DECLARE @update VARCHAR(1000)
SET @update = 'UPDATE dbo.DataModel_TEST_NUM SET dbo.DataModel_TEST_NUM.[' + @WKNUM + '] = dbo.TEST2.[New Value]
                  FROM dbo.TEST2 INNER JOIN dbo.DataModel_TEST_NUM ON dbo.TEST2.[Opportunity ID] = dbo.DataModel_TEST_NUM.opportunityID
                  WHERE (dbo.TEST2.WKNUM = ' + @WKNUM + ' )  '

EXEC(@update )

Upvotes: 0

Conrad Lotz
Conrad Lotz

Reputation: 8838

Typically you cannot use variables as column names as you are attempting:

Best would be to build the update string including all variables and then executing it once completed:

DECLARE @SqlString VARCHAR(1000)
SET @SqlString = 'UPDATE dbo.DataModel_TEST_NUM SET dbo.DataModel_TEST_NUM.[' + @WKNUM + '] = dbo.TEST2.[New Value]
                  FROM dbo.TEST2 INNER JOIN dbo.DataModel_TEST_NUM ON dbo.TEST2.[Opportunity ID] = dbo.DataModel_TEST_NUM.opportunityID
                  WHERE (dbo.TEST2.WKNUM = ' + @WKNUM + ' )  '

EXEC(@SqlString)

Upvotes: 1

Related Questions