CASE statement only if table exists

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

CASE statement only if table exists

Author

Topic

Kristen
Test

22859 Posts

Posted - 2004-11-19 : 08:05:15

I want to show COUNT(*) from history table, if it exists. (Basically we are using a staging table to get some data in, we rename the tables each day to suffix "_A" ... "_E", so that "_A" is yesterday's and "_E" is a few days ago - we pre-drop any existing "_E" table.

The idea is that we want be comfortable that the number of rows in today's table is about the same as yeterdays, so I want to do something like:


SELECT [A] = CASE WHEN EXISTS 
                    (SELECT * FROM sysobjects WHERE name = 'FOOBAR_A' AND type = 'U')
                  THEN 
                    (SELECT COUNT(*) FROM FOOBAR_A)
                  ELSE 
                    NULL 
                  END, 
... repeat for B - E ...

trouble is SQL appears to be trying to do the (SELECT COUNT(*) FROM FOOBAR_A) in all instances - i.e. even if the WHEN EXISTS is false

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'FOOBAR_A'.

Kristen

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-19 : 08:09:29

You'd have to use an IF construct:

DECLARE @a int
IF OBJECT_ID('FOOBAR_A') IS NOT NULL
SELECT @a=Count(*) FROM FOOBAR_A
.... etc.

Otherwise you'd have to go the dynamic SQL route.

Kristen
Test

22859 Posts

Posted - 2004-11-19 : 08:35:29

I was afraid of that ... there are about 50 tables to do :-(

Can I just grab the Count from some SysIndexes table instead? It doesn't have to be accurate up-to-the-minute as the COUNT won't have changed in 24 hours.

Kristen

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-19 : 08:38:40

Yeah, that's probably easier:

SELECT object_name(id) tbl, rows FROM sysindexes WHERE indid<2 AND object_name(id) like 'FOOBAR%'

Kristen
Test

22859 Posts

Posted - 2004-11-19 : 08:43:41

Cool, thanks.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources