Cursor

Code

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

















ALTER PROCEDURE [dbo].[CreateOrder] 

    -- Add the parameters for the stored procedure here

(

    @Name varchar(100),

    @Email varchar(200),

    @Phone varchar(13),

    @VehicleTemplateID int,

    @DateOrdered datetime,

    @OrderOptions varchar(max),

    @ID int out

)

AS

    DECLARE @Options AS TABLE ([Option] varchar(MAX))

--    DECLARE OptionCursor Cursor  

--      FOR SELECT [Option] FROM @Options

--    DECLARE @Option int

BEGIN TRANSACTION

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;



    INSERT INTO @Options([Option]) SELECT * FROM dbo.SplitWords(@OrderOptions);



    -- Insert statements for procedure here

    insert into [Orders]

    (Name, Email, Phone, VehicleTemplateID, DateOrdered )

    values

    (@Name, @Email, @Phone, @VehicleTemplateID, @DateOrdered )

    SET @ID = SCOPE_IDENTITY()



    --added by joey, 08.7.24, insert options into orders_options table

--    OPEN OptionCursor

--    FETCH NEXT FROM OptionCursor INTO @Option

--    WHILE (@@FETCH_STATUS <> -1)

--    BEGIN

--      IF (@@FETCH_STATUS <> -2)

--      BEGIN

--        INSERT INTO Orders_Options(OrderId, OptionId, Active)

--          VALUES(@ID, @Option, 1)

--      END

--      FETCH NEXT FROM OptionCursor INTO @Option

--    END

--    CLOSE OptionCursor

    INSERT INTO Orders_Options(OrderId, OptionId, Active)

      SELECT @ID, 

        *, 

        1 

        FROM @Options

    --end

    

    IF @@ERROR <> 0

    BEGIN

        --ROLL BACK THE TRANSACTION

        ROLLBACK

    

        --CREATE ERROR FOR FAILED UPDATE

        RAISERROR('Create of Order failed',16,1)

        RETURN

    END

    ELSE

    BEGIN

        exec SendCustomVehicleEmail @name, @email, @phone, @ID

    END

COMMIT



set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go






ALTER PROCEDURE [dbo].[CreateOrder] 
    -- Add the parameters for the stored procedure here
(
    @Name varchar(100),
    @Email varchar(200),
    @Phone varchar(13),
    @VehicleTemplateID int,
    @DateOrdered datetime,
    @OrderOptions varchar(max),
    @ID int out
)
AS
    DECLARE @Options AS TABLE ([Option] varchar(MAX))
    DECLARE OptionCursor Cursor  
      FOR SELECT [Option] FROM @Options
    DECLARE @Option int
BEGIN TRANSACTION
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    INSERT INTO @Options([Option]) SELECT * FROM dbo.SplitWords(@OrderOptions);

    -- Insert statements for procedure here
    insert into [Orders]
    (Name, Email, Phone, VehicleTemplateID, DateOrdered )
    values
    (@Name, @Email, @Phone, @VehicleTemplateID, @DateOrdered )
    SET @ID = SCOPE_IDENTITY()

    --added by joey, 08.7.24, insert options into orders_options table
    OPEN OptionCursor
    FETCH NEXT FROM OptionCursor INTO @Option
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
      IF (@@FETCH_STATUS <> -2)
      BEGIN
        INSERT INTO Orders_Options(OrderId, OptionId, Active)
          VALUES(@ID, @Option, 1)
      END
      FETCH NEXT FROM OptionCursor INTO @Option
    END
    CLOSE OptionCursor
    --end
    
    IF @@ERROR <> 0
    BEGIN
        --ROLL BACK THE TRANSACTION
        ROLLBACK
    
        --CREATE ERROR FOR FAILED UPDATE
        RAISERROR('Create of Order failed',16,1)
        RETURN
    END
    ELSE
    BEGIN
        exec SendCustomVehicleEmail @name, @email, @phone, @ID
    END
COMMIT

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON