Início / dba / Perguntas / 335393
Accepted
JoelFan
Asked: 2024-02-01 07:22:46 +0800 CST

Como projetar um SP de atualização que permita definir colunas como nulas e deixar as colunas inalteradas

  • 772

Digamos que eu tenha uma tabela chamada Customercom uma chave primária e colunas de dados como Namee DateOfBirth. Quero escrever um SP chamado que UpdCustomerByKeyreceba parâmetros CustomerKeye Name.DateOfBirth

O CustomerKeyparâmetro seria obrigatório, mas os parâmetros de dados seriam opcionais. Se não forem fornecidos, a coluna deverá permanecer inalterada.

Geralmente, isso é feito com algo como

CREATE PROCEDURE [UpdCustomer]
(
    @CustomerKey BIGINT,
    @Name NVARCHAR(100) = NULL,
    @DateOfBirth DATETIME = NULL
) AS
BEGIN
    UPDATE Customer SET
        Name = ISNULL(@Name, Name),
        DateOfBirth = ISNULL(@DateOfBirth, DateOfBirth)
    WHERE CustomerKey = @CustomerKey
END

Por aqui

UpdCustomer @CustomerKey=123, @DateOfBirth='2000-05-12'

será atualizado DateOfBirth, deixando Nameinalterado.

O problema com esse design é que ele não permite que o chamador defina colunas de dados como NULL. Se eu tentasse definir DateOfBirthassim NULL:

UpdCustomer @CustomerKey=123, @DateOfBirth=NULL

em vez de definir DateOfBirthcomo NULL, deixaria essa coluna inalterada.

Como mantenho esse design básico, mas permito definir colunas NULL?

sql-server

2 respostas

  1. Best Answer

    Uma maneira seria usar valores padrão para os parâmetros que de outra forma não seriam enviados. NULLé um caso de uso válido para você, então não pode ser NULL. Mas talvez uma string vazia e o DATETIMEvalor mínimo funcionem para você, assim?

    CREATE PROCEDURE [UpdCustomer]
(
    @CustomerKey BIGINT,
    @Name NVARCHAR(100) = '',
    @DateOfBirth DATETIME = '1753-01-01'
) AS
BEGIN
    UPDATE Customer SET
        Name = IIF(@Name = '', Name, @Name),
        DateOfBirth = IIF(@DateOfBirth = '1753-01-01', DateOfBirth, @DateOfBirth)
    WHERE CustomerKey = @CustomerKey
END
    • 2

    • Uma maneira é tornar @Nameos @DateOfBirthparâmetros obrigatórios, usando a abordagem get e set. Obtenha o existente e configure ou desative conforme necessário.

      CREATE OR ALTER PROCEDURE [UpdCustomer]
(
    @CustomerKey BIGINT,
    @Name NVARCHAR(100),
    @DateOfBirth DATETIME
  ) AS
  BEGIN
     UPDATE Customers SET
        Name = @Name,
        DateOfBirth = @DateOfBirth
     WHERE CustomerKey = @CustomerKey
  END

      O chamador deve definir todos eles, NULLou NOT NULL,

      EXEC [UpdCustomer] @CustomerKey = 1, @Name=NULL, @DateOfBirth = NULL
GO

EXEC [UpdCustomer] @CustomerKey = 1, @Name='Name01', @DateOfBirth = NULL
GO

EXEC [UpdCustomer] @CustomerKey = 2, @Name='Name02', @DateOfBirth = '01-31-2024'
GO

    • Se você pudesse dividi-los em duas chamadas, seria melhor usar dois processos diferentes.

      CREATE OR ALTER PROCEDURE [UpdCustomerName]
(
     @CustomerKey BIGINT,
     @Name NVARCHAR(100)= NULL
) AS
BEGIN
     UPDATE Customers SET
         Name = @Name
     WHERE CustomerKey = @CustomerKey
END

      CREATE OR ALTER PROCEDURE [UpdCustomerDateOfBirth]
(
     @CustomerKey BIGINT,
     @DateOfBirth DATETIME = NULL
) AS
BEGIN
     UPDATE Customers SET
         DateOfBirth = @DateOfBirth
     WHERE CustomerKey = @CustomerKey
END

    • A última opção seria tratá-los com SQL dinâmico em processo único.

    • 0

relate perguntas