Can I declare a SQL variable based on another table?

Question

Is it possible to set a varible based conditional values?

How I would explain it in layman's terms would be: I want my variable @L0 set up where if a CompanyCode = A then L0 = 1, If CompanyCode = B, then L0 = 2. Later in my Sproc I want to use that logic to assign a Score based on the conditions in variable L0. So it's like I want the variable L0 to be a whole condition statement where then each line will be assigned it's score based on looking at the CompanyCode.

Example of how I would think to do it, however I don't want to hard code any of the company code values.

declare @L0 int 
declare @L1 int =-1
declare @StartDate datetime
declare @EndDate datetime
set @L0 = (Select TRF from [Database1].[DQ].[RFRuleConfig] R 
    JOIN [Database2].[Dim].[Company] C on C.[CompanyCode] = R.[CompanyCode] 
    WHERE C.CompanyCode IN ('1857','1848'))
set @StartDate = dateadd(month, datediff(month, 0, getdate()) - 1, 0)  
set @EndDate = dateadd(month, datediff(month, 0, getdate()) + 0, -1) 

Data sample from [Database1].[DQ].[RFRuleConfig]. I want @L0 variable to be set to whatever is in TRF.

CompanyCode	TRF
1857	8
1848	16
1617	7

Alternatively, some sort of if statement might work where I do hard code values but can still use multiple. This is just not as ideal.

Ultimately, I need the @L0 value (which would be the TRF) to assign a score, based on the CompanyCode.@L0 is called many times in my Sproc and should behave dynamically so it will assign the score. If the CompanyCode is 1857 the Score (TRF) needs to be 8, if the CompanyCode is 1848 the score needs to be 16 etc.

select Recordidinv,CompanyCode,BookCountryID, 'RF1' as Error, @L1 as Score, from [Table]

Answer 1

You can use a table variable much like an actual table:

DECLARE @Table TABLE (CompanyCode INT, TRF INT);

INSERT INTO @Table (CompanyCode, TRF) 
SELECT 1857, 8 UNION ALL
SELECT 1848, 16 UNION ALL
SELECT 1617, 7;

SELECT *
  FROM @Table;

You can insert into it just like any other table, using VALUES or a SELECT statement. This table variable will only exist in the context of the executing batch, and is gone once the batch completes.

Answer 2

You can't directly assign the results of a query to a variable like:

declare @MyTable table = 
(
    select *
    from someQuery
)

It's just not valid syntax. However you can absolutely store results in table variables or temporary tables. You first have to declare/define the table object, and then insert the results you want into the table. So using your original query, here is how you could do it, using either construct.

Table Variable

declare @TableVar table
(
    CompanyCode int,
    TRF int
)

insert into @TableVar
(
    CompanyCode,
    TRF
)
select CompanyCode, TRF
from Database1.DQ.RFRuleConfig R
join Database2.Dim.Company C
    on C.CompanyCode = R.CompanyCode
where C.CompanyCode in
      (
          '1857',
          '1848',
          '1617'
      )

Temporary Table

drop table if exists #TempTable
create table #TempTable
(
    CompanyCode int,
    TRF int
)

insert into #TempTable
(
    CompanyCode
    TRF
)
select CompanyCode, TRF
from Database1.DQ.RFRuleConfig R
join Database2.Dim.Company C
    on C.CompanyCode = R.CompanyCode
where C.CompanyCode in
      (
          '1857',
          '1848',
          '1617'
      )

Once you've declared and populated one of these tables, at least for the duration of the procedure/batch, they behave just like any other table. You can join to them, insert into them further, update their results, or delete from them.

To re-use the table, let's say I need to update another table (say, TableIWantToUpdate) with the TRF value based on CompanyCode. You could do something like this:

update t 
set TRF = s.TRF
from TableIWantToUpdate t -- target
inner join @TableVar s --source
    on t.CompanyCode = s.CompanyCode

As Charlieface pointed out, you can also use a common table expression (CTE) or a derived table. The difference being those two approaches, the data construct only exists within the context of the query in which they are defined. So these are more useful if you only need to work with that data set once. If you want to use that lookup table repeatedly, a reusable copy of the data in a table variable or temp table is preferable.

Here's an example of the update above using a CTE:

;with cte as
(
    select CompanyCode, TRF
    from Database1.DQ.RFRuleConfig R
    inner join Database2.Dim.Company C
        on C.CompanyCode = R.CompanyCode
    where C.CompanyCode in
          (
              '1857',
              '1848',
              '1617'
          )
)
update t 
set TRF = s.TRF
from TableIWantToUpdate t -- target
inner join cte s --source
    on t.CompanyCode = s.CompanyCode

And another using a derived table:

update t 
set TRF = s.TRF
from TableIWantToUpdate t -- target
inner join
(
    select CompanyCode, TRF
    from Database1.DQ.RFRuleConfig R
    inner join Database2.Dim.Company C
        on C.CompanyCode = R.CompanyCode
    where C.CompanyCode in
          (
              '1857',
              '1848',
              '1617'
          )
) s --source
    on t.CompanyCode = s.CompanyCode