Detecting an existing table in a query

[Former Member]

Hi. I used to use the following as a part of a query where I used temporary tables. I'm aware that you can use the #prefix for this, but I want more control than that gives. This used to work under SQL 2000, but since we switched to 2008 it doesn't. We also switched to SAP B1 2007 at the same time, so that might also be the problem.

Can anyone tell me what I'm doing wrong here?

IF EXISTS (SELECT 1

FROM sysobjects

WHERE xtype='u' AND name='Temp6')

Drop Table Temp6

Sorry guys, it works anyway. Why is it that sometimes when something starts going wrong in the query wizard, funny things start to happen and things won't work until you logout and back in? That's happened different times to me.

Edited by: Neil Potter on Jun 25, 2010 12:21 AM

[Former Member]

Hi,

I am afraid your query would function like that since it only work if the temp table exist.

It is better you drop temp table every time after you complete your task with newly created temp table.

Thanks,

Gordon