This is an example of how not to be a crappy coder. Well done.


>>>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