MS SQL: How to get Employee Status from two seperate tables
create table #Table1
( EmpID CHAR(4),
  Co_Code CHAR(2),
  Co_ShortName CHAR(3),
  Emp_Status CHAR(2)
 )
INSERT INTO #Table1
    ([EmpID], [Co_Code], [Co_ShortName], [Emp_Status])
VALUES
    ('WC11', '01', 'AA1', 'FT'),
    ('KC22', '02', 'BB1', 'PT'),
    ('MA33', '03', 'CC1', 'FT');

CREATE TABLE #Table2
    (EmpID CHAR(4), 
     Co_Code CHAR(2), 
     Co_ShortName CHAR(3), 
     Emp_Status CHAR(2));

INSERT INTO #Table2
    ([EmpID], [Co_Code], [Co_ShortName], [Emp_Status])
VALUES
    ('WC11', '04', 'DD1', 'FT'),
    ('KC22', '05', 'EE1', 'PT'),
    ('MA33', '06', 'FF1', 'FT');
GO   


Version1:

DECLARE @c_EmpID CHAR(4) = '';
DECLARE @c_Emp_Status CHAR(2) = '';

SET @c_EmpID = 'WC11'
--SET @c_EmpID = 'KC22'

SELECT 
        CASE WHEN T1.Emp_Status = 'FT' THEN T1.Co_Code 
             WHEN T1.Emp_Status = 'PT' THEN T2.Co_Code 
             ELSE 'N/A' 
        END AS Co_Code,
        CASE WHEN T1.Emp_Status = 'FT' THEN T1.Co_Shortname 
            WHEN T1.Emp_Status = 'PT' THEN T2.Co_Shortname 
            ELSE 'N/A' 
        END AS Co_Shortname
    FROM #Table1 AS T1
        LEFT JOIN #Table2 AS T2
            ON T1.EmpID = T2.EmpID
            --AND T2.Emp_Status = 'PT'
    --WHERE T1.EmpID = @c_EmpID


output:

Co_Code Co_Shortname
01      AA1


Version2: You can put the logic inside stored procedures:

CREATE PROCEDURE #usp_Co_FT
(
  @i_c_EmpID CHAR(4)
)
AS
BEGIN
        SELECT 
           T1.Co_Code ,T1.Co_Shortname
        FROM #Table1 AS T1 
        WHERE
            T1.EmpID = @i_c_EmpID
            AND T1.Emp_Status = 'FT'
END
GO
CREATE PROCEDURE #usp_Co_PT
(
  @i_c_EmpID CHAR(4)
)
AS
BEGIN
        SELECT 
           T2.Co_Code , T2.Co_Shortname
        FROM #Table2 AS T2
        WHERE
            T2.EmpID = @i_c_EmpID
            AND T2.Emp_Status ='PT'
END
GO


DECLARE @c_EmpID CHAR(4) = '';
DECLARE @c_Emp_Status CHAR(2) = '';

SET @c_EmpID = 'WC11'
--SET @c_EmpID = 'KC22'

SELECT @c_Emp_Status =  T1.Emp_Status FROM #Table1 AS T1 WHERE T1.EmpID = @c_EmpID

IF @c_Emp_Status = 'FT'
BEGIN   
    EXEC  #usp_Co_FT @i_c_EmpID = @c_EmpID
END
ELSE IF @c_Emp_Status = 'PT'
    BEGIN
        EXEC  #usp_Co_PT @i_c_EmpID = @c_EmpID
    END
GO


Version 3 with UNION ALL

DECLARE @c_EmpID CHAR(4) = 'WC11';
--SET @c_EmpID = 'KC22'


SELECT 
   T1.Co_Code ,T1.Co_Shortname
FROM #Table1 AS T1 
WHERE
    T1.EmpID = @c_EmpID
    AND T1.Emp_Status = 'FT'

UNION ALL  

SELECT 
   T2.Co_Code , T2.Co_Shortname
FROM #Table2 AS T2
WHERE
    T2.EmpID = @c_EmpID
    AND T2.Emp_Status ='PT'


dbfiddle here