Select from a table that might not exist

Question

Select from a table that might not exist

nkat

SSChasing Mays

Points: 654
More actions
June 6, 2022 at 10:27 pm

#4045283
Hello!
I'm writing a query. As per the logic in my SELECT statement there should be a column from a table that might exist. If it does not I should select Null instead
Smth. like the following non compiling code
```
DECLARE @NoTable bit
SET @NoTable = 1 
SELECT 
  e.SN as ESN,
  CASE WHEN @NoTable = 'True' Null
  CASE WHEN @NoTable = 'False' NE.SN 
  END as NESN
FROM tblExist E, tblDoesntExist NE
```
The problem with this approach is that if [tblDoesntExist] is not there, the script can not be complied
Would someone please suggest a way to solve this
- This topic was modified 2 years, 3 months ago by nkat.
- This topic was modified 2 years, 3 months ago by nkat.
- This topic was modified 2 years, 3 months ago by nkat.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

Does the table actually exist and you're just checking to see if the column exists or will the table actually be missing?

In either case, I'm pretty sure that you're going to need two separate queries or Dynamic SQL.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

nkat SSChasing Mays Points: 654 More actions · Answer 2

Thank you Jeff, for your follow-up!

The entire table might exist or not. The reason for that—versions of the particular app, so the query must be just one but run in both scenarios.

Would you please elaborate on the dynamic SQL approach?

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

nkat wrote:

Thank you Jeff, for your follow-up!
The entire table might exist or not. The reason for that—versions of the particular app, so the query must be just one but run in both scenarios.
Would you please elaborate on the dynamic SQL approach?

The dynamic SQL won't be examined at compile or run time so a missing table won't cause a failure. Neither will a missing column.

Basically, you can write some code to check sys.columns to see if the column exists. Even if the table does exist, it sound like you still want it to drive around that without failing. So you simply build the query you need as dynamic SQL based on whether the column is available or not. Another way is to have the two queries in separate stored procedures and decide which one to call based on that same column existence check.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Ola_Boyd Newbie Points: 6 More actions · Answer 4

Ola_Boyd

Newbie

Points: 6

June 7, 2022 at 8:40 am

#4045497

Nice! THank you for help

nkat SSChasing Mays Points: 654 More actions · Answer 5

Thank you, Jeff! I'll look into dynamic approach.

DesNorton SSC-Insane Points: 23842 More actions · Answer 6

You can do something like this for the dynamic SQL

DECLARE @SQL nvarchar(MAX);

IF OBJECT_ID(N'dbo.tblDoesntExist', N'U') IS NULL
BEGIN
  -- TABLE does not exist
  SET @SQL = N'
  SELECT  ESN  = E.SN
        , NESN = NULL
  FROM    dbo.tblExist AS E;';
END;
ELSE
BEGIN
  -- TABLE does exist
  SET @SQL = N'
  SELECT      ESN  = E.SN
            , NESN = NE.SN
  FROM        dbo.tblExist       AS E
  CROSS JOIN  dbo.tblDoesntExist AS NE;';
END;

EXEC sys.sp_executesql @stmt = @SQL;

How to post data/code on a forum to get the best help.