question

sqlLearner 1 avatar image
sqlLearner 1 asked

Check if table Exists in JOIN

Not sure if this is possible and this might be a stupid question but I figured it couldn't hurt to ask. Is it possible to check if a table exists before Joining to it? for example like this Select * from dbo.Master t1 JOIN dbo.table2 t2 ON t1.ID= t2.ID IF OBJECT_ID('dbo.t_TABLE3') IS NOT NULL BEGIN LEFT JOIN dbo.t_TABLE3' t3 ON t1.ID= t3.ID END
sql-server-2008tsqljoinsnot-exists
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
No. You'd have to check for it's existence as part of a logic branch in the code, and then run separate queries depending on whether it did or not. I'm intrigued? Why would you want to do such a thing? Whatever consumes the results wouldn't know how many/what columns it was going to get back.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I am running queries on imported files but one of the files isn't always given and I want to make this task into a stored procedure that i just need to execute once the files are imported. I think the simplest way is to have a sp that creates the table for me with all the same column names. And I will run it when I know I am not getting that file and basically just create a empty table.
0 Likes 0 ·
Yes that sounds better - handle the missing file at import time, rather than having to deal with differing scenarios later on
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
There are potential pitfalls with writing conditional code like this - particularly if you try to port it to other versions of SQL Server. In order to protect yourself against code compilation / run-time errors, you'll need to do something like: DECLARE @SQLCommand varchar(max) SELECT @SQLCommand = 'SELECT * FROM dbo.master t1 JOIN dbo.Table2 t2 ON t1.ID = t2.ID ' IF OBJECT_ID('dbo.t_Table3') IS Not NULL BEGIN SELECT @SQLCommand = @SQLCommand + 'LEFT JOIN dbo.t_Table3 t3 ON t1.ID= t3.ID ' END EXEC (@SQLCommand) Something like that, anyway. Build up your query as a separate string based on what objects are available, then `EXEC` it. A pain, to program, maintain and keep performance optimised, and generally a clue that something, somewhere, has gone wrong with the design of the system.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.