Conditional WHEREs if columns exist

Question

I have several tables which typically have nullable "CreatedDate" and/or "ModifiedDate" datetime columns. I would like a query that can select from any single table where one, both, or neither datetime column exist and each column is BETWEEN provided (separate) ranges.

Here is a rough (non-working) example. This attempts to use COL_LENGTH to determine if a column exists. However, if a column is not present an error is thrown, I believe because the entire WHERE is still evaluated.

SELECT * FROM tableName
WHERE 1=1 AND
(COL_LENGTH(tableName, 'CreatedDate') IS NULL OR CreatedDate BETWEEN @CreatedFrom AND @CreatedTo) AND
(COL_LENGTH(tableName, 'ModifiedDate') IS NULL OR ModifiedDate BETWEEN @ModifiedFrom AND @ModifiedTo)

This is solvable with dynamic SQL. However, in this case I am restricted to read-focussed permissions. So no EXEC, CREATE, or INSERT.

Is this possible with static SQL?

Example tables

With both columns:

DECLARE @Table1 table
(
    Id int NOT NULL,
    CreatedDate datetime NULL,
    ModifiedDate datetime NULL
);
INSERT @Table1
    (Id, CreatedDate, ModifiedDate)
VALUES
    (1, NULL, NULL),
    (2, NULL, '20230101 01:23:45.678'),
    (3, '20230101 02:34:56.789', NULL),
    (4, '20230101 03:45:00.891', '20230101 04:56:00.789');
DECLARE 
    @CreatedFrom datetime = {TS '2023-01-01 01:30:00.000'},
    @CreatedTo datetime = {TS '2023-12-31 23:30:00.000'},
    @ModifiedFrom datetime = {TS '2023-01-01 06:00:00.000'},
    @ModifiedTo datetime = {TS '2023-12-31 18:00:00.000'};

With one column:

DECLARE @Table2 table
(
    Id int NOT NULL,
    CreatedDate datetime NULL
);
INSERT @Table2
    (Id, CreatedDate)
VALUES
    (1, NULL),
    (2, '20230101 02:34:56.789');
DECLARE 
    @CreatedFrom datetime = {TS '2023-01-01 01:30:00.000'},
    @CreatedTo datetime = {TS '2023-12-31 23:30:00.000'};

^{Thank you @PaulWhite for the initial example.}

ypercubeᵀᴹ · Accepted Answer · 2023-10-08T14:28:52.943

Yes, this tricky problem can be solved in plain SQL, with a rather obscure method.

For more detailed explanation and the original idea, see the answers from Andriy M and me in two similar questions:
Why can't I use a case statement to see if a column exists and not select from it?
How to select specific rows if a column exists or all rows if a column doesn't

In your case - and with the assumptions

the two columns (when they exist) are not nullable and
when none of the columns exists, we want to return all rows
when one of the two columns exists, we apply the criteria only for that column

This would work:

SELECT x.*
FROM 
    ( SELECT NULL AS CreatedDate,
             NULL AS ModifiedDate
    ) AS dummy
  CROSS APPLY
    ( SELECT t.*
      FROM dbo.tableName AS t
      WHERE ( CreatedDate BETWEEN @Date1 AND @Date2    -- the columns should
           OR CreatedDate IS NULL )                    -- be referenced
        AND ( ModifiedDate BETWEEN @Date3 AND @Date4   -- WITHOUT
           OR ModifiedDate IS NULL )                   -- a table prefix here
    ) AS x ;

score 4 · Answer 2 · answered Oct 08 '23 at 18:28

It's possible to use XML for this:

DECLARE @Table table
(
    Col1 integer NOT NULL,
    CreatedDate datetime NULL
);
INSERT @Table
    (Col1, CreatedDate)
VALUES
    (1, '20230101 12:34:56.789'),
    (2, '20230101 01:23:45.678');
DECLARE 
    @CreatedFrom datetime = {TS '2023-01-01 01:30:00.000'},
    @CreatedTo datetime = {TS '2023-12-31 23:30:00.000'},
    @ModifiedFrom datetime = {TS '2023-01-01 06:00:00.000'},
    @ModifiedTo datetime = {TS '2023-12-31 18:00:00.000'};

SELECT 
    T.*
FROM @Table AS T
CROSS APPLY (SELECT T.* FOR XML PATH, TYPE) AS A (x)
WHERE 
    1 = 1
    AND 1 = A.x.exist('
        row
        [
            not(CreatedDate) (: column does not exist :)
            or 
            (
                CreatedDate >= sql:variable("@CreatedFrom")
                and CreatedDate <= sql:variable("@CreatedTo")
            )
        ]'
    )
    AND 1 = A.x.exist('
        row
        [
            not(ModifiedDate) (: column does not exist :)
            or 
            (
                ModifiedDate >= sql:variable("@ModifiedFrom")
                and ModifiedDate <= sql:variable("@ModifiedTo")
            )
        ]'
    );

Conditional WHEREs if columns exist

Example tables

2 Answers2