Procedure: [AdventureWorks2016CTP3].[Demo].[usp_DemoInitSeed]

CollapseAll image

Collapse image Procedure properties


 Name   Value 
 Schema   [Demo] 
 Owner   [dbo] 
 Creation date   23.10.2015 
 Type   P 
 Encrypted   
 ID   1620200822 
 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
@items_per_order   int 4 Input  

Collapse image Objects that [Demo].[usp_DemoInitSeed] depends on


Object name Object type Dep level
[Demo] Schema 1
[Demo].[DemoSalesOrderDetailSeed] Table 1
[Demo].[DemoSalesOrderHeaderSeed] Table 1
[Sales].[SalesOrderHeader_inmem] Table 1
[Sales].[SpecialOfferProduct_inmem] Table 1
Total 5 object(s)

Collapse image SQL


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE Demo.usp_DemoInitSeed @items_per_order int = 5
AS
BEGIN
    DECLARE @ProductID int, @SpecialOfferID int,
        @i int = 1
    DECLARE @seed_order_count int = (SELECT COUNT(*)/@items_per_order FROM Sales.SpecialOfferProduct_inmem)

    DECLARE seed_cursor CURSOR FOR 
        SELECT 
            ProductID,
            SpecialOfferID 
        FROM Sales.SpecialOfferProduct_inmem

    OPEN seed_cursor

    FETCH NEXT FROM seed_cursor 
    INTO @ProductID, @SpecialOfferID

    BEGIN TRAN

        DELETE FROM Demo.DemoSalesOrderHeaderSeed

        INSERT INTO Demo.DemoSalesOrderHeaderSeed
        (
            DueDate,
            CustomerID,
            SalesPersonID,
            BillToAddressID,
            ShipToAddressID,
            ShipMethodID
        )
        SELECT
            dateadd(d, (rand(BillToAddressID*CustomerID)*10)+1,cast(sysdatetime() as date)),
            CustomerID,
            SalesPersonID,
            BillToAddressID,
            ShipToAddressID,
            ShipMethodID
        FROM Sales.SalesOrderHeader_inmem


        WHILE @@FETCH_STATUS = 0
        BEGIN
            INSERT Demo.DemoSalesOrderDetailSeed
            SELECT 
                @i % 6 + 1,
                @ProductID,
                @SpecialOfferID,
                @i % (@seed_order_count+1)

            SET @i += 1

            FETCH NEXT FROM seed_cursor 
            INTO @ProductID, @SpecialOfferID
        END

        CLOSE seed_cursor
        DEALLOCATE seed_cursor
    COMMIT

    UPDATE STATISTICS Demo.DemoSalesOrderDetailSeed
    WITH FULLSCAN, NORECOMPUTE
END
GO

Collapse image See also


List of procedures