Início / dba / Perguntas / 331944
Accepted
camtech
Asked: 2023-10-08 10:35:26 +0800 CST

WHEREs condicionais se existirem colunas

  • 772

Eu tenho várias tabelas que normalmente possuem colunas de data e hora "CreatedDate" e/ou "ModifiedDate" anuláveis. Eu gostaria de uma consulta que pudesse selecionar qualquer tabela única onde existisse uma, ambas ou nenhuma coluna de data e hora e cada coluna recebesse BETWEENintervalos (separados).

Aqui está um exemplo aproximado (não funcional). Isso tenta usar COL_LENGTHpara determinar se existe uma coluna. Porém, se uma coluna não estiver presente, será gerado um erro, acredito porque o todo WHEREainda é avaliado.

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)

Isso pode ser resolvido com SQL dinâmico. No entanto, neste caso estou restrito a permissões focadas na leitura. Portanto, não EXEC, CREATE ou INSERT.

Isso é possível com SQL estático?

Tabelas de exemplo

Com ambas as colunas:

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'};

Com uma coluna:

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'};

Obrigado @PaulWhite pelo exemplo inicial .

sql-server

2 respostas

  1. Best Answer

    Sim, esse problema complicado pode ser resolvido em SQL simples, com um método bastante obscuro.

    Para uma explicação mais detalhada e a ideia original, veja as respostas de Andriy M e minhas em duas perguntas semelhantes:
    Por que não posso usar uma instrução case para ver se existe uma coluna e não selecionar a partir dela?
    Como selecionar linhas específicas se existir uma coluna ou todas as linhas se não existir uma coluna

    No seu caso - e com as suposições

    • as duas colunas (quando existem) não são anuláveis ​​e
    • quando nenhuma das colunas existe, queremos retornar todas as linhas
    • quando uma das duas colunas existe, aplicamos os critérios apenas para essa coluna

    Isso funcionaria:

    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 ;
    • 4

  2. É possível usar XML para isso:

    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")
            )
        ]'
    );
    • 4

relate perguntas