SQL PROCEDURE 3

-- GRANT ALL ON EMP_FETCH TO PUBLIC.   
-- EXEC EMP_FETCH   
CREATE PROC EMP_FETCH   
@empcode varchar(6) 
AS   
BEGIN   
CREATE TABLE #EMP   
(   
EMP_CODE CHAR(6) NULL,   
EMP_NAME VARCHAR(50) NULL,   
EMP_ADD VARCHAR (150) NULL,   
DESG VARCHAR(50)NULL,               
DEPT VARCHAR(50)NULL,   
JOBCODE VARCHAR(10) NULL,   
SAL NUMERIC(8,2) NULL,   
TYP VARCHAR(10) NULL,   
CASH NUMERIC(8,2) NULL,   
DOB DATETIME NULL,   
DOJ DATETIME NULL   
)   
   
INSERT INTO #EMP(EMP_CODE,EMP_NAME,DESG,DEPT,JOBCODE,TYP,DOB)               
SELECT B.H01_EMP_NUM,ISNULL(A.H01_FIRST_NAME,'')+' '+ISNULL(A.H01_MIDDLE_NAME,'')+' '+A.H01_LAST_NAME,C.C02_FUNCTION_DESC,    
D.C12_POSITIONDESC,B.C10_Job_code,B.C08_Catg_code,A.H01_BIRTH_DATE             
FROM  H01_IDENTFCATN A, H10_EMPASGN B, C02_FUNCTION_DETAILS C, C12_POSITION D                     
WHERE A.H01_EMP_NUM  = @empcode 
 and A.H01_EMP_NUM  = B.H01_EMP_NUM                     
 AND  B.C02_FUNCTION_CODE = C.C02_FUNCTION_CODE                    
 AND B.C12_POSITIONCODE = D.C12_POSITIONCODE                    
 AND B.H10_EFFECTIVE_DATE_TO IS NULL            
 AND B.C01_ASGN_REASON_TYPE NOT IN ('T')             
ORDER BY B.H01_EMP_NUM            
   
UPDATE #EMP   
SET EMP_ADD = ISNULL(B.H02_ADDRESS1,'')+' '+ISNULL(B.H02_ADDRESS2,'')+' '+B.H02_ADDRESS3   
FROM H02_ADDRESS B, #EMP C             
WHERE C.EMP_CODE = B.H02_ADDRESS_OF   
   
UPDATE #EMP   
SET SAL = B.CTC   
FROM SALARY_APPROVAL1 B, #EMP C             
WHERE C.EMP_CODE = B.H01_EMP_NUM   
   
UPDATE #EMP   
SET CASH = B.CASH   
FROM SALARY_APPROVAL1 B, #EMP C             
WHERE C.EMP_CODE = B.H01_EMP_NUM   
   
UPDATE #EMP   
SET DOJ = B.H10_EFFECTIVE_DATE_FROM   
FROM H10_EMPASGN B, #EMP C             
WHERE C.EMP_CODE = B.H01_EMP_NUM   
AND B.C01_ASGN_REASON_TYPE = 'NW'   
   
SELECT * FROM #EMP   
   
END   
   
-- EXEC EMP_FETCH '30651'