T-SQL Get Values from Lookup table and use in view / stored procedure

Question

I couldn't find that via search, so I guess I am not asking it the right way, so help is welcome.

We have a lookup table:

Id   Name
------------------
1    "Test"
2    "New"
3    "InProgress"

Table2:

StatusId  SomethingElse
  1   
  2

Table 1

ID  Other Other StatusId (Fkey to Table2) ...

Then we have a view that selects from several tables and one of the columns is a CASE Statement:

    SELECT * FROM Table1 t1 -- has million records

    CASE When t1.StatusId = 1 THEN (SELECT Name from LOOKUP table where ID = 1) END --'Test'
    CASE When t1.StatusId = 2 THEN (SELECT Name from LOOKUP table where ID = 2) END --'New'
CASE When t3.Date is not null THEN (SELECT Name from LOOKUP table where ID = 3) END --'In Progress'
-- AND ALSO the case look at other tables another 5-6 tables and there are conditions from there
INNER JOIN Table2 t2 on ...
INNER JOIN Table3 t3 on ...

As you see these are really static values.

I want to load them once into variables, e.g.

 @LookUp1 = SELECT [NAME] FROM LookUP WHERE Id = 1, 
 @LookUp2 = SELECT [NAME] FROM LookUP WHERE Id = 2 

and replace the select in the CASE statement to this:

When StatusId = 1 THEN @LookUp
When StatusId = 2 THEN @LookUp2

The view loops through millions of records and it gets really slow to do the select from Lookup table for every row.

Answer 1

Why not simply use a join?

SELECT <columns list from main table>, Lt.Name
FROM <main table> As Mt -- Do not use such aliases in real code!
JOIN <SecondaryTable> As St -- this represents your Table3
    ON <condition> 
[LEFT] JOIN <Lookup table> As Lt
    ON Mt.StatusId = Lt.Id
    OR (Lt.Id = 3 AND St.Date is not null)

Of course, replace <columns list from main table> with the actual columns list, <main table> with the name of the main table and so on.

The join might be an inner or left join, depending on the nullability of the StatusId column in the main table and if it's nullable, on what you want to get in such cases (either a row with null name or no row at all).

I've put together a little demonstration to show you exactly what I mean.

Create and populate sample tables (Please save us this step in your future questions):

CREATE TABLE LookUp (Id int, Name varchar(10));

INSERT INTO LookUp (Id, Name) VALUES
(1, 'Test'), (2, 'New'), (3, 'InProgress');


CREATE TABLE Table1 (Id int not null, StatusId int null);

INSERT INTO Table1(Id, StatusId)
SELECT n, CASE WHEN n % 3 = 0 THEN NULL ELSE (n % 3) END
FROM
(
    SELECT TOP 30 ROW_NUMBER() OVER(ORDER BY @@SPID) As n
    FROM sys.objects
) tally

CREATE TABLE Table3
(
    Id int not null,
    Date date null
)
INSERT INTO Table3 (Id, Date) 
SELECT Id,  CASE WHEN StatusId IS NULL AND Id % 4 = 0 THEN GetDate() END
FROM Table1

The query:

SELECT  Table1.Id, 
        Table1.StatusId, 
        Table3.Date,
        LookUp.Name
FROM Table1
JOIN Table3
    ON Table1.Id = Table3.Id
LEFT JOIN LookUp 
    ON Table1.StatusId = LookUp.Id
    OR (LookUp.Id = 3 AND Table3.Date IS NOT NULL)

Results:

Id  StatusId    Date            Name
1   1           NULL            Test
2   2           NULL            New
3   NULL        NULL            NULL
4   1           NULL            Test
5   2           NULL            New
6   NULL        NULL            NULL
7   1           NULL            Test
8   2           NULL            New
9   NULL        NULL            NULL
10  1           NULL            Test
11  2           NULL            New
12  NULL        27.06.2019      InProgress
13  1           NULL            Test
14  2           NULL            New
15  NULL        NULL            NULL
16  1           NULL            Test
17  2           NULL            New
18  NULL        NULL            NULL
19  1           NULL            Test
20  2           NULL            New
21  NULL        NULL            NULL
22  1           NULL            Test
23  2           NULL            New
24  NULL        27.06.2019      InProgress
25  1           NULL            Test
26  2           NULL            New
27  NULL        NULL            NULL
28  1           NULL            Test
29  2           NULL            New
30  NULL        NULL            NULL

You can also see a live demo on rextester.

Answer 2

Create a SQL function which return Name according to Id.

Create FUNCTION [dbo].[GetLookUpValue] 
(
@Id int
)
RETURNS varchar(500)
AS BEGIN
    return(Select Name from LOOKUP_table with(nolock) where Id=@Id)
END