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?
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