Using local temporary table

Hello Every body!

Look at the bellow picture, I have 2 database steps: 1. For the first step, it'll create a LOCAL temporary table and store the data in this table (For ex: It is named '#SUPERIOR') 2. The seconds step, it'll get data from '#SUPERIOR'

But, we have some strange behaviors that sometimes it can not get data from '#SUPERIOR' temporary table. I guess that the '#SUPERIOR' is already dropped (because connection is closed --> local temporary table is dropped) before step 2 is executed.

Our application have a lot of users that can access concurrency (more than 1000 users)

Could you pls give me some ideas?

Thanks for your help!

This is sample of step1:

Declare @ORSID int =1;
  DECLARE @EMPKEY int = 20018;
  DECLARE @CONNUMB varchar = '1';
  DECLARE @SUPERIORORLID int = 1;
  DECLARE @PARENTID int = 0;

  IF OBJECT_ID('tempdb..#SUPERIORORGTREEANDROLE_eRAP20018') IS NOT NULL
    DROP TABLE  #SUPERIORORGTREEANDROLE_eRAP20018

  DECLARE @ORGTREEANDROLE TABLE(
    ORGID INT, ORGPNTOU INT, ORGCHDOU INT,ORSID INT, 
    OUNDESC varchar(MAX), OUNTYPE varchar(MAX), 
    ASNID INT, EMPKEY INT, CONNUMB varchar(MAX), ASNMAIN varchar(MAX),
    ORLID INT
  );
  INSERT INTO @ORGTREEANDROLE
  SELECT org.ORGID, org.ORGPNTOU, org.ORGCHDOU, org.ORSID, 
        ounc.OUNDESC, ounc.OUNTYPE, 
        asnc.ASNID, asnc.EMPKEY, asnc.CONNUMB, asnc.ASNMAIN,
        asr.ORLID

  FROM HRMORG org 
  LEFT OUTER JOIN HRMOUN ounc ON ounc.OUNKEY = org.ORGCHDOU
  LEFT OUTER JOIN HRMASN asnc ON asnc.OUNKEY = ounc.OUNKEY  and  asnc.ASNFROM <= CONVERT(DATE, GETDATE()) and  asnc.ASNUNTIL >= CONVERT(DATE, GETDATE())
  LEFT OUTER JOIN HRMASR asr ON asr.ASNID = asnc.ASNID
  WHERE  org.ORSID = @ORSID and ounc.OUNFROM <= CONVERT(DATE, GETDATE()) and ounc.OUNUNTIL >= CONVERT(DATE, GETDATE())
  ORDER BY org.ORGID


  SELECT @PARENTID = orgTree.ORGPNTOU FROM @ORGTREEANDROLE orgTree 
    WHERE orgTree.CONNUMB = @CONNUMB AND orgTree.EMPKEY = @EMPKEY AND orgTree.ASNMAIN = 'Y'

  DECLARE @COUNT INT = 0;

  WHILE (@COUNT < 20)
  BEGIN

    SET @COUNT = @COUNT + 1;
    IF (@PARENTID = 0 OR @PARENTID = NULL ) 
    BEGIN
        BREAK;
    END
    ELSE
    BEGIN
        IF ((SELECT COUNT(ORGID)FROM @ORGTREEANDROLE orgTree 
        WHERE orgTree.ORGCHDOU = @PARENTID AND orgTree.ORLID = @SUPERIORORLID) > 0)
        BEGIN
            SELECT orgTree.EMPKEY, orgTree.CONNUMB, orgTree.ORGCHDOU ,orgTree.ASNID
            INTO #SUPERIORORGTREEANDROLE_eRAP20018
            FROM @ORGTREEANDROLE orgTree 
            WHERE orgTree.ORGCHDOU = @PARENTID
                AND orgTree.ORLID = @SUPERIORORLID
            BREAK;
        END
    END
    SELECT @PARENTID = orgTree.ORGPNTOU 
    FROM @ORGTREEANDROLE orgTree 
    WHERE orgTree.ORGCHDOU = @PARENTID

  END

This is the sample for step2:

SELECT * FROM #SUPERIORORGTREEANDROLE_eRAP20018;

Regards,