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
Go to Top of Page

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

Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -