Procedure: [AdventureWorks2016CTP3].[Sales].[usp_InsertSalesOrder_inmem]

CollapseAll image

Collapse image Procedure properties


 Name   Value 
 Schema   [Sales] 
 Owner   [dbo] 
 Creation date   23.10.2015 
 Type   P 
 Encrypted   
 ID   1780201392 
 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/output or output  
@DueDate   datetime2 8 Input  
@CustomerID   int 4 Input  
@BillToAddressID   int 4 Input  
@ShipToAddressID   int 4 Input  
@ShipMethodID   int 4 Input  
@SalesOrderDetails   [Sales].[SalesOrderDetailType_inmem] 0 Input
@Status   tinyint 1 Input  
@OnlineOrderFlag   bit 1 Input  
@PurchaseOrderNumber   nvarchar 25 Input  
@AccountNumber   nvarchar 15 Input  
@SalesPersonID   int 4 Input  
@TerritoryID   int 4 Input  
@CreditCardID   int 4 Input  
@CreditCardApprovalCode   varchar 15 Input  
@CurrencyRateID   int 4 Input  
@Comment   nvarchar 128 Input  

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


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

Collapse image SQL


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE Sales.usp_InsertSalesOrder_inmem
    @SalesOrderID int OUTPUT,
    @DueDate [datetime2](7) NOT NULL,
    @CustomerID [int] NOT NULL,
    @BillToAddressID [int] NOT NULL,
    @ShipToAddressID [int] NOT NULL,
    @ShipMethodID [int] NOT NULL,
    @SalesOrderDetails Sales.SalesOrderDetailType_inmem READONLY,
    @Status [tinyint] NOT NULL = 1,
    @OnlineOrderFlag [bit] NOT NULL = 1,
    @PurchaseOrderNumber [nvarchar](25) = NULL,
    @AccountNumber [nvarchar](15) = NULL,
    @SalesPersonID [int] NOT NULL = -1,
    @TerritoryID [int] = NULL,
    @CreditCardID [int] = NULL,
    @CreditCardApprovalCode [varchar](15) = NULL,
    @CurrencyRateID [int] = NULL,
    @Comment nvarchar(128) = NULL
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC WITH
  (TRANSACTION ISOLATION LEVEL = SNAPSHOT,
   LANGUAGE = N'us_english')

    DECLARE @OrderDate datetime2 NOT NULL = SYSDATETIME()

    DECLARE @SubTotal money NOT NULL = 0

    SELECT @SubTotal = ISNULL(SUM(p.ListPrice * (1 - ISNULL(so.DiscountPct, 0))),0)
    FROM @SalesOrderDetails od 
        JOIN Production.Product_inmem p on od.ProductID=p.ProductID
        LEFT JOIN Sales.SpecialOffer_inmem so on od.SpecialOfferID=so.SpecialOfferID

    INSERT INTO Sales.SalesOrderHeader_inmem
    (    DueDate,
        Status,
        OnlineOrderFlag,
        PurchaseOrderNumber,
        AccountNumber,
        CustomerID,
        SalesPersonID,
        TerritoryID,
        BillToAddressID,
        ShipToAddressID,
        ShipMethodID,
        CreditCardID,
        CreditCardApprovalCode,
        CurrencyRateID,
        Comment,
        OrderDate,
        SubTotal,
        ModifiedDate)
    VALUES
    (    
        @DueDate,
        @Status,
        @OnlineOrderFlag,
        @PurchaseOrderNumber,
        @AccountNumber,
        @CustomerID,
        @SalesPersonID,
        @TerritoryID,
        @BillToAddressID,
        @ShipToAddressID,
        @ShipMethodID,
        @CreditCardID,
        @CreditCardApprovalCode,
        @CurrencyRateID,
        @Comment,
        @OrderDate,
        @SubTotal,
        @OrderDate
    )

    SET @SalesOrderID = SCOPE_IDENTITY()

    INSERT INTO Sales.SalesOrderDetail_inmem
    (
        SalesOrderID,
        OrderQty,
        ProductID,
        SpecialOfferID,
        UnitPrice,
        UnitPriceDiscount,
        ModifiedDate
    )
    SELECT 
        @SalesOrderID,
        od.OrderQty,
        od.ProductID,
        od.SpecialOfferID,
        p.ListPrice,
        ISNULL(p.ListPrice * so.DiscountPct, 0),
        @OrderDate
    FROM @SalesOrderDetails od 
        JOIN Production.Product_inmem p on od.ProductID=p.ProductID
        LEFT JOIN Sales.SpecialOffer_inmem so on od.SpecialOfferID=so.SpecialOfferID

END
GO

Collapse image See also


List of procedures