>>I don't understand why you "lost" temporary table.
>>I use tow ways:
>>1) dynamic SQL with EXECUTE
>>2) OLE-DB
>
>This page explains it all :
>
>https://www.sommarskog.se/dynamic_sql.html
>
>The section "Temp Tables and Dynamic SQL" is exactly the situation.
>
>Have you found a way to use such approach without having to create the temporary table before so it would be in the scope?
>
>OpenRowSet() has to be used because we have to import some Excel data.

Hmm,

1) global temporary table

CREATE OR ALTER PROCEDURE dbo._mishap_sp(@lcTMP NVARCHAR(128))
AS
 DECLARE @lcSQL NVARCHAR(4000)
 SELECT @lcSQL=REPLACE(N'SELECT * INTO ##%TMP% FROM [INFORMATION_SCHEMA].[TABLES]', '%TMP%', @lcTMP)
 EXEC sp_executesql @lcSQL
 --SELECT * FROM #temp
GO

EXEC dbo._mishap_sp 'mytmp'
select * from  ##mytmp
drop table ##mytmp
GO

2) cursor parameter

DECLARE @lorsData CURSOR, @lcSQL NVARCHAR(MAX)

    SET @lcSQL='SET @loCursor = CURSOR LOCAL READ_ONLY FOR 
      SELECT *
        FROM '+@lcTABLENAME+' AA INNER JOIN #OBJECTs  AB ON AA.DATABASEID=AB.DATABASEID AND AA.OBJECTID=AB.OBJECTID AND ....
        WHERE AA.EVENTCLASS=44
        ORDER BY AA.EVENTSEQUENCE
        ;
       OPEN @loCursor'
 END

 EXEC sp_executesql @lcSQL, N'@loCursor CURSOR OUTPUT', @lorsData OUTPUT

 WHILE 0 = 0 BEGIN
   FETCH NEXT FROM @lorsData INTO @liEVENTSEQUENCE, ...
   IF @@FETCH_STATUS <> 0 BREAK


END
 CLOSE @lorsData
 DEALLOCATE @lorsData

MartinaJ