Procedure: [AdventureWorks2016CTP3].[Sales].[usp_InsertSalesOrder_ondisk]

CollapseAll image

Collapse image Procedure properties


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


Object name Object type Dep level
[Sales] Schema 1
[Production].[Product_ondisk] Table 1
[Sales].[SalesOrderDetail_ondisk] Table 1
[Sales].[SalesOrderHeader_ondisk] Table 1
[Sales].[SpecialOffer_ondisk] Table 1
[Sales].[SalesOrderDetailType_ondisk] Datatype 1
Total 6 object(s)

Collapse image SQL


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE Sales.usp_InsertSalesOrder_ondisk
    @SalesOrderID int OUTPUT,
    @DueDate [datetime2](7) ,
    @CustomerID [int] ,
    @BillToAddressID [int] ,
    @ShipToAddressID [int] ,
    @ShipMethodID [int] ,
    @SalesOrderDetails Sales.SalesOrderDetailType_ondisk READONLY,
    @Status [tinyint]  = 1,
    @OnlineOrderFlag [bit] = 1,
    @PurchaseOrderNumber [nvarchar](25) = NULL,
    @AccountNumber [nvarchar](15) = NULL,
    @SalesPersonID [int] = -1,
    @TerritoryID [int] = NULL,
    @CreditCardID [int] = NULL,
    @CreditCardApprovalCode [varchar](15) = NULL,
    @CurrencyRateID [int] = NULL,
    @Comment nvarchar(128) = NULL
AS
BEGIN 
    BEGIN TRAN
    
        DECLARE @OrderDate datetime2 = sysdatetime()

        DECLARE @SubTotal money = 0

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

        INSERT INTO Sales.SalesOrderHeader_ondisk
        (    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_ondisk
        (
            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_ondisk p on od.ProductID=p.ProductID
            LEFT JOIN Sales.SpecialOffer_ondisk so on od.SpecialOfferID=so.SpecialOfferID

    COMMIT
END
GO

Collapse image See also


List of procedures