>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 >>
>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 > >Thanks, option 1 works.