Хранимая процедура с временной таблицей

---

Я хочу изменить хранимую процедуру без жесткого кодирования имени документа и получить тот же результат.

Что я уже пробовал:

Мой ИП-это:

ALTER PROCEDURE [dbo].[SPEMPIDDETAILS]
AS
BEGIN
    DECLARE @INTSTART AS INTEGER;
    DECLARE @INTSTOP AS INTEGER;

    DECLARE @ENAME AS VARCHAR(50);

    DECLARE @PREFNO AS VARCHAR(50);
    DECLARE @PVAL AS DATE;

    DECLARE @VREFNO AS VARCHAR(50);
    DECLARE @VVAL AS DATE;

    DECLARE @SBREFNO AS VARCHAR(50);
    DECLARE @SBVAL AS VARCHAR(50);

    DECLARE @UVREFNO AS VARCHAR(50);
    DECLARE @UVVAL AS DATE;

    --Temp Table for Save Employee Name
    DECLARE @TEMPTABLEEMPNAME TABLE (INTEMPNAME_ID INT IDENTITY(1,1),
                                     EMPNAME VARCHAR(50))

    --Temp Table for Save ID Ref.No and Validity
    DECLARE @TEMPTABLEEMP TABLE (INTEMPID_ID INT IDENTITY(1,1),
                                 ENAME VARCHAR(50),
                                 PREFNO VARCHAR(50), PVAL DATE, 
                                 VREFNO VARCHAR(50), VVAL DATE,
                                 SBREFNO VARCHAR(50), SBVAL DATE,
                                 UVREFNO VARCHAR(50), UVVAL DATE)

    INSERT INTO @TEMPTABLEEMPNAME(EMPNAME)
        SELECT EMPLOYEE
        FROM EMPIDDETAILS

    SELECT @INTSTART = 1;
    SELECT @INTSTOP = (SELECT COUNT(INTEMPNAME_ID) FROM @TEMPTABLEEMPNAME);

    WHILE @INTSTART <= @INTSTOP
    BEGIN
        SET @ENAME = (SELECT EMPLOYEE 
                      FROM EMPIDDETAILS 
                      WHERE PK_ID = @INTSTART)

        SELECT 
            @PREFNO = (SELECT REF_NO FROM EMPIDDETAILS 
                       WHERE DOCUMENT_TYPE = 'PASSPORT' AND EMPLOYEE = @ENAME),
            @PVAL = (SELECT VALIDITY FROM EMPIDDETAILS 
                     WHERE DOCUMENT_TYPE = 'PASSPORT' AND EMPLOYEE = @ENAME)

        SELECT 
            @VREFNO = (SELECT REF_NO FROM EMPIDDETAILS 
                       WHERE DOCUMENT_TYPE = 'VISA' AND EMPLOYEE = @ENAME),
            @VVAL = (SELECT VALIDITY FROM EMPIDDETAILS 
                     WHERE DOCUMENT_TYPE = 'VISA' AND EMPLOYEE = @ENAME)

        SELECT 
            @SBREFNO = (SELECT REF_NO FROM EMPIDDETAILS 
                        WHERE DOCUMENT_TYPE = 'SEAMAN BOOK' AND EMPLOYEE = @ENAME),
            @SBVAL = (SELECT VALIDITY FROM EMPIDDETAILS 
                      WHERE DOCUMENT_TYPE = 'SEAMAN BOOK' AND EMPLOYEE = @ENAME)

        SELECT 
            @UVREFNO = (SELECT REF_NO FROM EMPIDDETAILS 
                        WHERE DOCUMENT_TYPE = 'USVISA' AND EMPLOYEE = @ENAME),
            @UVVAL = (SELECT VALIDITY FROM EMPIDDETAILS 
                      WHERE DOCUMENT_TYPE = 'USVISA' AND EMPLOYEE = @ENAME)


        INSERT INTO @TEMPTABLEEMP 
        VALUES(@ENAME, @PREFNO, @PVAL, @VREFNO, @VVAL, @SBREFNO, @SBVAL, @UVREFNO, @UVVAL)

        SET @ENAME = 0
        SET @PREFNO = 0
        SET @PVAL = NULL
        SET @VREFNO = 0
        SET @VVAL = NULL
        SET @SBREFNO = 0
        SET @SBVAL = NULL
        SET @UVREFNO = 0
        SET @UVVAL = NULL

        SET @INTSTART = @INTSTART + 1;
    END

    SELECT DISTINCT 
        ENAME AS EMPLOYEE, 
        PREFNO AS REF_NO, PVAL as VALIDITY,
        VREFNO AS REF_NO, VVAL AS VALIDITY,
        SBREFNO AS REF_NO, SBVAL AS VALIDITY,
        UVREFNO AS REF_NO, UVVAL AS VALIDITY
    FROM 
        @TEMPTABLEEMP
END

---