Good afternoon! I'm having some problems with some of my stored procedures, which I am praying someone can help me with:

1. When ever I set an SQL statement into a variable and execute it I am unable to make use of a temporary table.

Declare @SQL nvarchar(4000)

Set @SQL="Select * into #tmp from Orders"
execute(@SQL)
Select * from #tmp --> This line generates an error #tmp does not exist.

Because of this I have to resort to making use of...

Set @SQL="Select * into tmp from Orders" --> which is no longer a temp table.

Because of this I have to DROP TABLE tmp at the end of the stored procedure. Things work our just fine unless an error is encountered when the procedure is executed and thus tmp does not get DROPped! Which brings me to my next problem.

2. To check if a table is existing I issue the following in my stored procedure:

If Exists (select * from sysobjects where name='tmp' and type='U')
Drop Table tmp

This commands execute properly except when I manually drop the table from the SQL Server Enterprise Manager. In which case sysobjects does not get updated. And so, when the If Exist... gets executed, it returns TRUE. And since the table physically does not exist, DROP TABLE... generates an error.

Is there a better way of checking if a table exist?