Procedure: [AdventureWorks2016CTP3].[Sales].[usp_UpdateSalesOrderShipInfo_inmem]

CollapseAll image

Collapse image Procedure properties


 Name   Value 
 Schema   [Sales] 
 Owner   [dbo] 
 Creation date   23.10.2015 
 Type   P 
 Encrypted   
 ID   1524200480 
 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_inmem] depends on


Object name Object type Dep level
[Sales] Schema 1
[Sales].[usp_UpdateSalesOrderShipInfo_native] Procedure 1
Total 2 object(s)

Collapse image SQL


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
-- wrapper stored procedure that contains retry logic to deal with update conflicts
-- alternatively, the client can perform retries in case of conflicts

-- 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_inmem
    @SalesOrderID int , 
    @ShipDate datetime2 = NULL,
    @Comment nvarchar(128) = NULL,
    @Status tinyint,
    @TaxRate smallmoney = 0,
    @Freight money,
    @CarrierTrackingNumber nvarchar(25)
AS
BEGIN

  DECLARE @retry INT = 10
  SET @ShipDate = ISNULL(@ShipDate, SYSDATETIME())

  WHILE (@retry > 0)
  BEGIN
    BEGIN TRY

      EXEC Sales.usp_UpdateSalesOrderShipInfo_native
        @SalesOrderID = @SalesOrderID, 
        @ShipDate = @ShipDate,
        @Comment = @Comment,
        @Status = @Status,
        @TaxRate = @TaxRate,
        @Freight = @Freight,
        @CarrierTrackingNumber = @CarrierTrackingNumber


      SET @retry = 0
    END TRY
    BEGIN CATCH
      SET @retry -= 1
  
      IF (@retry > 0 AND error_number() in (41302, 41305, 41325, 41301))
      BEGIN

        IF XACT_STATE() <> 0 
          ROLLBACK TRANSACTION

      END
      ELSE
      BEGIN
        ;THROW
      END
    END CATCH
  END
END
GO

Collapse image See also


List of procedures