Home / Questions / 138172
Accepted
ger
Asked: 2020-02-14 06:56:07 +0800 CST 2020-02-14 06:56:07 +0800 CST

How to update the value of a specific column in SQL

  • 772

the question is how can I update the value of a specific column in SQL without having to send data from the other fields, I explain myself with the code.

I have the following Stored Procedure

ALTER PROCEDURE [dbo].[spUpdateCredito]
 @CreditoID int,
 @PersonaID int,
 @NumeroCuotas int,
 @EstadoCredito int,
 @MontoAprovado decimal (18,2),
 @Plazo int,
 @FechaCredito datetime,
 @Tasa decimal (4,2)
AS
 BEGIN
  UPDATE Credito SET
      PersonaID = @PersonaID,
      NumeroCuotas = @NumeroCuotas,
      EstadoCredito = @EstadoCredito,
      MontoAprovado = @MontoAprovado,
      Plazo = @Plazo,
      FechaCredito = @FechaCredito,
     Tasa = @Tasa
  WHERE
   CreditoID = @CreditoID
  END

in a routine you need to change ONLY CreditStatus but I don't want to update the other columns.

If I run it from sql manager it would be like this:

EXEC    @return_value = [dbo].[spUpdateCredito]
    @CreditoID = 3,
    @PersonaID = NULL,
    @NumeroCuotas = NULL,
    @EstadoCredito = 5,
    @MontoAprovado = NULL,
    @Plazo = NULL,
    @FechaCredito = NULL,
    @Tasa = NULL

 SELECT 'Return Value' = @return_value

but I don't want it to leave me columns in null

If I only want the specific column to be updated, I run the script

EXEC    @return_value = [dbo].[spUpdateCredito]
    @CreditoID = 3,
    @EstadoCredito = 5


SELECT  'Return Value' = @return_value

I get the following error

Msg 201, Level 16, State 4, Procedure spUpdateCredito, Line 0 [Batch Start Line 2] The procedure or function 'spUpdateCredito' expected the parameter '@PersonaID', which was not specified.

Requesting the other columns, how would it be to update the specific column

UPDATE IS NOT CREATE Another routine or script is to use this one.

sql

2 Answers

  1. The stored routine is made to update all the fields, so it would be better to create a new routine to update only that data and pass fewer parameters or condition within the procedure based on null values. for instance

    if isnull(@parametroX) and isnull(parametroY) then
//consulta de actualizacion de cierta cantidad de campos
else
//consulta de actualizacion de todos los campos
end if;
    • 1
  2. Best Answer

    In the first place, you get the error because the Sp parameters expect you to pass them a value, so that it is not necessary to do so, you must define a default value for each one, for example:

    ALTER PROCEDURE [dbo].[spUpdateCredito]
          @CreditoID int,  -- Este debiera ser el único obligatorio
          @PersonaID int = NULL,
...

    And so with each parameter. Now you have the problem that by not defining a parameter its value will be NULLwhich is something we don't want since we are using it in a UPDATE, one possibility is to ask IFif the value is NOT NULLand then fire the UPDATEonly for that countryside:

    IF @PersonaID IS NOT NULL
BEGIN
  UPDATE Credito SET
         PersonaID = @PersonaID
         WHERE CreditoID = @CreditoID
END

    The problem with this is that we are surely running multiple UPDATEones for each field. The alternative to this is to use a single statement of UPDATEbut with ISNULLin each parameter, in case it is, we will NULLupdate with the same value:

      UPDATE Credito SET
         PersonaID = ISNULL(@PersonaID, PersonaID),
         ...
         WHERE CreditoID = @CreditoID

    With these two tips your SP could look like this:

    ALTER PROCEDURE [dbo].[spUpdateCredito]
 @CreditoID int, -- Este debiera ser el único obligatorio
 @PersonaID int = NULL,
 @NumeroCuotas int = NULL,
 @EstadoCredito int = NULL,
 @MontoAprovado decimal (18,2) = NULL,
 @Plazo int = NULL,
 @FechaCredito datetime = NULL,
 @Tasa decimal (4,2) = NULL
AS
 BEGIN
  UPDATE Credito SET
      PersonaID = ISNULL(@PersonaID,PersonaID),
      NumeroCuotas = ISNULL(@NumeroCuotas,NumeroCuotas)
      EstadoCredito = ISNULL(@EstadoCredito,EstadoCredito)
      MontoAprovado = ISNULL(@MontoAprovado,MontoAprovado)
      Plazo = ISNULL(@Plazo,Plazo)
      FechaCredito = ISNULL(@FechaCredito,FechaCredito)
      Tasa = ISNULL(@Tasa, Tasa)
  WHERE
   CreditoID = @CreditoID
  END

    One more comment, if we wanted to be super picky, there are certain situations that we would eventually want to avoid:

    1. Let all the parameters beNULL
    2. That the values ​​of the parameters are identical to those in the table

    Clearly in these two cases it would not have made sense to do the UPDATE, a simple way to control it is in the same statement of UPDATE, adding more conditions to the WHERE:

    WHERE
   CreditoID = @CreditoID
   AND ( 
          ( (PersonaId IS NULL AND @PersonaID IS NOT NULL) OR PersonaID <> ISNULL(@PersonaID, PersonaID))
          OR ( (NumeroCuotas IS NULL AND @NumeroCuotas IS NOT NULL) OR NumeroCuotas <> ISNULL(@NumeroCuotas, NumeroCuotas))
          ...
       )

    And finally it is important to clarify that this way of updating the data would not eventually allow a column to be modified so that it is the same NULL, that is, for example "null" FechaCredito, this can be solved but the logic would have to be modified a bit, using some "Flag that "force" him NULLin these cases.

    • 1