How to UPDATE multiple values in T-SQL with the stored procedure-CodePudding

I have a table like that:

CREATE TABLE Ticket
(
    id_Ticket int PRIMARY KEY,
    id_Passengers int,
    id_Schedule int,
    Ticket_Price money,
    Nomer_Mest int
)

DECLARE and EXEC:

DECLARE @Ticket_Price int, @id_Ticket int;
SET @Ticket_Price = 50 
SET @id_Ticket = 9

EXEC AlterTicket @Ticket_Price, @id_Ticket

This is my stored procedure:

CREATE PROCEDURE AlterTicket
    @Ticket_Price Money, @id_Ticket int, @Nomer_Mest int;
AS
    UPDATE Ticket 
    SET Ticket_Price = @Ticket_Price
    WHERE id_Ticket = @id_Ticket

    UPDATE Ticket 
    SET Nomer_Mest = @Nomer_Mest 
    WHERE id_Ticket = @id_Ticket

I also have to assign a value for 2 parameters at once. But let's say I need to change only Ticket_Price, I won't change the parameters of the Stored procedure.

CodePudding user response：

I'd start by changing the order of parameters, and you can check for NULL:

CREATE PROCEDURE AlterTicket
    @id_Ticket int, @Nomer_Mest int, @Ticket_Price Money
AS
    UPDATE Ticket 
    SET Ticket_Price = COALESCE(@Ticket_Price, Ticket_Price), Nomer_Mest = COALESCE(@Nomer_Mest, Nomer_Mest) 
    WHERE id_Ticket = @id_Ticket;

Call it with:

EXEC AlterTicket @id_Ticket, @Nomer_Mest, NULL;

Or:

EXEC AlterTicket @id_Ticket, NULL, @Ticket_Price;

As I said in a comment, I also strongly advice to consider using decimal instead of money.

CodePudding user response：

You can update multiple values in a single update:

CREATE PROCEDURE AlterTicket
    @Ticket_Price Money, @id_Ticket int, @Nomer_Mest int;
AS
    UPDATE Ticket 
    SET Ticket_Price = @Ticket_Price, Nomer_Mest = @Nomer_Mest
    WHERE id_Ticket = @id_Ticket;

CodePudding user response：

I am not sure if you meant this:

CREATE PROCEDURE AlterTicket
    @id_Ticket int, @Ticket_Price Money = null, @Nomer_Mest int = null
AS
begin
  if @Ticket_Price is not null and @Nomer_mest is not null
    UPDATE Ticket 
    SET Ticket_Price = @Ticket_Price, Nomer_Mest = @Nomer_Mest
    WHERE id_Ticket = @id_Ticket;
  else if @Ticket_Price is not null
    UPDATE Ticket 
    SET Ticket_Price = @Ticket_Price
    WHERE id_Ticket = @id_Ticket;
  else if @Nomer_Mest is not null
    UPDATE Ticket 
    SET Nomer_Mest = @Nomer_Mest
    WHERE id_Ticket = @id_Ticket;
end

Here is DBFiddle demo.

This has advantage that, on a long list of parameters you can pass only the needed ones.