Protect view against non existing table

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

Transact-SQL (2012)

Protect view against non existing table

Author

Topic

ArnoldG
Starting Member

36 Posts

Posted - 2015-02-19 : 14:23:32

Hi,
I have a view built out of 3 joins to 3 tables.
In some cases the 3rd table does not exist (some users do not have this 3rd table)
Is there a way to protect the view from giving an error, even if this table does not exist?

I am using only 1 column from this table3 and I would like to have a NULL in that specific column.


CREATE VIEW [MYVIEW] AS
SELECT 
   T1.A,
   T2.A,
   T3.A --(a NULL value if the table does not exists)

FROM Table1 T1
   JOIN Table2 T2
   JOIN Table4 T3 --(if it exists)
GO

Is there a way to accomplish this?

Thx.

cstokes91
Yak Posting Veteran

72 Posts

Posted - 2015-02-19 : 14:45:36

Yes, you would do something like this: Have separate views depending on if the table exists.


IF OBJECT_ID('database.dbo.tablename', 'U') IS NOT NULL
CREATE VIEW [MYVIEW] AS
SELECT 
   T1.A,
   T2.A,
   T3.A --(a NULL value if the table does not exists)

FROM Table1 T1
   JOIN Table2 T2
   JOIN Table4 T3 --(if it exists)
GO

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-02-19 : 16:24:21

No real way to do that.

Easiest would be to create an empty table, and change the current INNER JOIN to a LEFT OUTER JOIN for that table:


CREATE VIEW [MYVIEW] AS
SELECT 
   T1.A,
   T2.A,
   T3.A --(a NULL value if the table does not exists)

FROM Table1 T1
   INNER JOIN Table2 T2
   LEFT OUTER JOIN Table4 T3 --(if it exists)
GO

ArnoldG
Starting Member

36 Posts

Posted - 2015-02-20 : 03:27:03

Thanks for both your answers.
Since I am not able to create an empty table I think this solution will work best for me:

IF OBJECT_ID('database.dbo.tablename', 'U') IS NOT NULL

Thanks again.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-02-20 : 10:33:50

That's not valid syntax, but using dynamic SQL you could create a different view.

You could also create a synonym with that name rather than an actual table that pointed to an empty table with a different name.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources