SQL: how can I set a variable in this context?

Question

I'm trying to set a couple of variables, one is a name of the table (@table), the other one is a string.

I've got the error message:

Must declare the table variable "@table".

as far as I could understand it's because I must use @table as a table variable, but I just need it as a string

declare @a varchar(50);
declare @table varchar(100);
select @table =
    case
        WHEN Version = 'Advanced' THEN ("tableadv")
        WHEN Version = 'Professional' THEN ("tablepro")
        WHEN Version = 'Light' THEN ("tablelight")
        WHEN Version = 'Short' THEN ("tableshort")
    END 
FROM partno where inpn=3

set @a = (select top (1) LicenseNumber from @table where used is null)
insert into seriali (LicenseNumber, idpn, serdgtrace)
select @a, 2, 'DAT-enrico'
update @table set used = 1 where LicenseNumber=@a

any help will be appreciated. Many thanks, enrico

Possible duplicate of [Table name as variable](https://stackoverflow.com/questions/2838490/table-name-as-variable) — EzLo, Feb 15 '19 at 09:26

Yogesh Sharma · Answer 1 · 2019-02-14T17:34:10.193

0

You need table variable not only variable to store more values with more columns not just single value :

declare @table table ( 
    @col int,
    . . . 
  )

insert into @table (col)
      select case WHEN Version = 'Advanced' THEN 'tableadv'
                  WHEN Version = 'Professional' THEN 'tablepro'
                  WHEN Version = 'Light' THEN 'tablelight'
                  WHEN Version = 'Short' THEN 'tableshort'
             end 
      from partno 
      where inpn = 3

edited Feb 14 '19 at 17:34

answered Feb 14 '19 at 17:29

Yogesh Sharma

49,870
5
26
52

Nope - as Ezlo noted this is a case for dynamic sql. – SMor Feb 15 '19 at 13:40

score 0 · Answer 2 · answered Feb 15 '19 at 09:10

hello Yogesh and thanks for the answer,

anyway I need this case structure to pick the right table name based on a field in another table.

Then I have to pass this value to update the right table;

set @a = (select top (1) LicenseNumber from @table where used is null)
update @table set used = 1 where LicenseNumber=@a

I don't need to build a table variable with one of the case values in a record, because I won't be able to pass the right table name either

Many thanks, Enrico

SQL: how can I set a variable in this context?

2 Answers2