Início / dba / Perguntas / 40694
Accepted
nojetlag
Asked: 2013-04-24 05:09:44 +0800 CST

Cláusula WHERE condicional em uma função com valor de tabela

  • 772

Eu tenho uma função com valor de tabela

    CREATE FUNCTION [dbo].[DateRange] 
    (
        @StartDate date,
        @EndDate date,
        @Location varchar(25),
        @Device varchar(25)
    ) 
    RETURNS TABLE 
    AS
    RETURN 
    (
        SELECT *
        FROM MyTable
        WHERE Date < @EndDate AND
              Date > @StartDate AND
              Location = @Location AND
              Device = @Device 

    );

Agora eu gostaria de dar ao usuário a opção de usar NULL como parâmetro de localização e/ou parâmetro de dispositivo para que ele receba todos os dados independentes da localização e do dispositivo de volta

Qual seria a maneira mais elegante e eficiente de fazer isso?

t-sql sql-server-2012

3 respostas

  1. Best Answer

    Igual a :

    CREATE FUNCTION [dbo].[DateRange] 
    (
        @StartDate date,
        @EndDate date,
        @Location varchar(25) = NULL,
        @Device varchar(25) = NULL
    ) 
    RETURNS TABLE 
    AS
    RETURN 
    (
        SELECT *
        FROM MyTable
        WHERE 
          Date < @EndDate 
          AND Date > @StartDate 
          AND ( @Location IS NULL OR (@Location IS NOT NULL AND Location = @Location))
          AND ( @Device IS NULL OR (@Device IS NOT NULL AND Device = @Device ))

    )

    SE as colunas devicee locationpuderem ser NULL, você pode querer considerar esta opção:

    CREATE FUNCTION [dbo].[DateRange] 
    (
        @StartDate date,
        @EndDate date,
        @Location varchar(25) = NULL,
        @Device varchar(25) = NULL
    ) 
    RETURNS TABLE 
    AS
    RETURN 
    (
        SELECT *
        FROM MyTable
        WHERE 
          Date < @EndDate 
          AND Date > @StartDate 
          AND ( @Location IS NULL OR Location = @Location)
          AND ( @Device IS NULL OR Device = @Device )

    )

    Isso funcionaria se você estivesse usando um tipo de tabela UD para o local, como você perguntou em seu comentário. Apenas certifique-se de que o parâmetro de entrada seja somente leitura. No entanto, você não pode passar NULL para um parâmetro do tipo tabela, você deve passar um objeto de parâmetro vazio desse tipo:

    CREATE TYPE LocationsType AS TABLE 
( LocationName VARCHAR(25));

CREATE TABLE MyTable (Date datetime, Location varchar(25), Device varchar(25));

CREATE FUNCTION [dbo].[DateRange] 
    (
        @StartDate date,
        @EndDate date,
        @Location LocationsType READONLY,
        @Device varchar(25) = NULL
    ) 
    RETURNS TABLE 
    AS
    RETURN 
    (
        SELECT *
        FROM MyTable
        WHERE 
          Date < @EndDate 
          AND Date > @StartDate 
          AND ( ((select count(*) FROM @Location) = 0) OR Location IN (select LocationName from @Location))
          AND ( @Device IS NULL OR Device = @Device )

    )

    Chame-o:

    declare @l LocationsType

select *
from [dbo].[DateRange] (getDate(), GetDate(), @l,NULL)
    • 4

  2. A maneira mais eficiente de acordo com meus próprios testes é:
    *Observação: válido apenas para colunas NON-NULLABLE, conforme comentado por Aaron.

    CREATE FUNCTION [dbo].[DateRange] 
(
    @StartDate date,
    @EndDate date,
    @Location varchar(25),
    @Device varchar(25)
) 
RETURNS TABLE 
AS
RETURN 
(
    SELECT *
    FROM MyTable
    WHERE Date < @EndDate 
      AND Date > @StartDate 
      AND Location = CASE WHEN @Location IS NULL THEN Location ELSE @Location END
      AND Device = CASE WHEN @Device IS NULL THEN Device ELSE @Device END

);

    Existe outra forma, usando ISNULL(@Location,Location)ao invés de CASEexpressão, porém, por mais elegante que seja, é menos eficiente então CASE.

    • 3

  3. Tudo bem, encontrei uma maneira de fazer o mesmo com tipos com valor de tabela

    CREATE FUNCTION [dbo].[DateRange] 
    (
        @StartDate date,
        @EndDate date,
        @Location TVT.Location ReadOnly,
        @Device TVT.Device ReadOnly
    ) 
    RETURNS TABLE 
    AS
    RETURN 
    (
        SELECT *
        FROM MyTable
        WHERE Date < @EndDate 
              Date > @StartDate AND 
              (Location IN (SELECT Location FROM @Location) OR NOT EXISTS(SELECT 1 from @Location)) AND
              (Device IN (SELECT Device FROM @Device) OR NOT EXISTS(SELECT 1 FROM @Device))

    )
    • 1

relate perguntas