How to assign result of OUTPUT DELETED to an output parameter?

Question

I have the following query that generates the next available bill of lading numbers.

ALTER PROCEDURE [dbo].[GetNextTruckBol]
    @FacilityId INT,
    @Count INT = 1
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE Facilities
    SET NextTruckBol = NextTruckBol + @Count
    OUTPUT DELETED.NextTruckBol
    WHERE Id = @FacilityId
END

But now I need to modify it so that the resulting value is instead assigned to an OUTPUT parameter.

I know how to declare the OUTPUT parameter. How can I assign the value of OUTPUT DELETED.NextTruckBol to that parameter?

Answer 1

Per the UPDATE documentation

Variable names can be used in UPDATE statements to show the old and new values affected, but this should be used only when the UPDATE statement affects a single record. If the UPDATE statement affects multiple records, to return the old and new values for each record, use the OUTPUT clause.

So if you know that the UPDATE will only affect max one row (as Id is constrained to be unique) and you don't want to mess around with table variables you can use

SET @variable = column, column = expression, which sets the variable to the pre-update value of the column.

So that would be

CREATE OR ALTER PROCEDURE [dbo].[GetNextTruckBol]
    @FacilityId INT,
    @Count INT = 1,
    @NextTruckBol INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE Facilities
    SET @NextTruckBol = NextTruckBol,
        NextTruckBol = NextTruckBol + @Count
    WHERE Id = @FacilityId
END