Procedure: [AdventureWorks2016CTP3].[Sales].[usp_UpdateSalesOrderShipInfo_ondisk]

CollapseAll image

Collapse image Procedure properties


 Name   Value 
 Schema   [Sales] 
 Owner   [dbo] 
 Creation date   23.10.2015 
 Type   P 
 Encrypted   
 ID   1540200537 
 Implementation type   Transact SQL 
 Is native compiled   

Collapse image Creation options


Name Value
QUOTED_IDENTIFIER ON
ANSI_NULLS ON

Collapse image Parameters


Name  Description Datatype  Max length  Type  ReadOnly
@SalesOrderID   int 4 Input  
@ShipDate   datetime2 8 Input  
@Comment   nvarchar 128 Input  
@Status   tinyint 1 Input  
@TaxRate   smallmoney 4 Input  
@Freight   money 8 Input  
@CarrierTrackingNumber   nvarchar 25 Input  

Collapse image Objects that [Sales].[usp_UpdateSalesOrderShipInfo_ondisk] depends on


Object name Object type Dep level
[Sales] Schema 1
[Sales].[SalesOrderDetail_ondisk] Table 1
[Sales].[SalesOrderHeader_ondisk] Table 1
Total 3 object(s)

Collapse image SQL


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
-- for simplicity, we assume all items in the order are shipped in the same package, and thus have the same carrier tracking number
CREATE PROCEDURE Sales.usp_UpdateSalesOrderShipInfo_ondisk
    @SalesOrderID int , 
    @ShipDate datetime2 = NULL,
    @Comment nvarchar(128) = NULL,
    @Status tinyint,
    @TaxRate smallmoney = 0,
    @Freight money,
    @CarrierTrackingNumber nvarchar(25)
AS
BEGIN
  SET @ShipDate = ISNULL(@ShipDate, SYSDATETIME())

  BEGIN TRAN
    DECLARE @now datetime2 = SYSDATETIME()

    UPDATE Sales.SalesOrderDetail_ondisk 
    SET CarrierTrackingNumber = @CarrierTrackingNumber, ModifiedDate = @now
    WHERE SalesOrderID = @SalesOrderID

    UPDATE Sales.SalesOrderHeader_ondisk
    SET RevisionNumber = RevisionNumber + 1,
        ShipDate = @ShipDate,
        Status = @Status,
        TaxAmt = SubTotal * @TaxRate,
        Freight = @Freight,
        ModifiedDate = @now
    WHERE SalesOrderID = @SalesOrderID
  COMMIT

END
GO

Collapse image See also


List of procedures