---------------------------------------------------------------------
-- SQL Server 2005XgAhvV[WvO~O
-- 6 G[
---------------------------------------------------------------------

---------------------------------------------------------------------
-- 6.1 RAISERROR Xe[gg
---------------------------------------------------------------------

RAISERROR(N'G[܂', 0, 1)


EXEC sp_addmessage 50001,
                   16,
                   N'Unable to update Total of LeaseSchedule',
                   'us_english',
                   'true'
GO
EXEC sp_addmessage 50001,
                   16,
                   N'[XXPW[̑zXVł܂',
                   'Japanese'


RAISERROR (50001, 16, 1)


RAISERROR (50001, 16, 1) WITH LOG


EXEC sp_addmessage 50002,
                   16
                   N'Unable to update %s.',
                   'us_english',
                   'true'
GO
EXEC sp_addmessage 50002,
                   16,
                   N'%1!XVł܂',
                   'Japanese'


RAISERROR (50002, 16, 1, N'LeaseSchedulee[u')


CREATE PROCEDURE dbo.ap_LeasedAsset_Insert1
-- [Xi}A[XXPW[̑zXV
-- sSȃ\[V̗
(
    @intEqId INT,
    @intLocationId INT,
    @intStatusId INT,
    @intLeaseId INT,
    @intLeaseScheduleId INT,
    @intOwnerId INT,
    @mnyLease MONEY,
    @intAcquisitionTypeID INT
)
AS
    SET NOCOUNT ON

    BEGIN TRANSACTION

    -- ȋ}
    INSERT dbo.Inventory(
        EqId,
        LocationId,
        StatusId,
        LeaseId,
        LeaseScheduleId,
        OwnerId,
        Lease,
        AcquisitionTypeID
    )
    VALUES(
        @intEqId,
        @intLocationId,
        @intStatusId,
        @intLeaseId,
        @intLeaseScheduleId,
        @intOwnerId,
        @mnyLease,
        @intAcquisitionTypeID
    )

    -- z̍XV
    UPDATE dbo.LeaseSchedule
    SET PeriodicTotalAmount = PeriodicTotalAmount + @mnyLease
    WHERE LeaseId = @intLeaseId

    COMMIT TRANSACTION
    RETURN

---------------------------------------------------------------------
-- 6.3 @@ERROR gG[
---------------------------------------------------------------------

CREATE PROCEDURE dbo.ap_LeasedAsset_Insert1
-- [Xi}A[XXPW[̑zXV
-- ܂Ƃ͌Ȃ\[V̗
(
    @intEqId INT,
    @intLocationId INT,
    @intStatusId INT,
    @intLeaseId INT,
    @intLeaseScheduleId INT,
    @intOwnerId INT,
    @mnyLease MONEY,
    @intAcquisitionTypeID INT
)
AS
    SET NOCOUNT ON

    BEGIN TRANSACTION
    -- ȋ}
    INSERT Inventory(
        EqId,
        LocationId,
        StatusId,
        LeaseId,
        LeaseScheduleId,
        OwnerId,
        Lease,
        AcquisitionTypeID
    )
    VALUES(
        @intEqId,
        @intLocationId,
        @intStatusId,
        @intLeaseId,
        @intLeaseScheduleId,
        @intOwnerId,
        @mnyLease,
        @intAcquisitionTypeID
    )

    IF @@ERROR <> 0
    BEGIN
        PRINT N'\ȂG[܂'
        ROLLBACK TRANSACTION
        RETURN 1
    END

    -- z̍XV
    UPDATE LeaseSchedule
    SET PeriodicTotalAmount = PeriodicTotalAmount + @mnyLease
    WHERE LeaseId = @intLeaseId

    IF @@ERROR <> 0
    BEGIN
        PRINT N'\ȂG[܂'
        ROLLBACK TRANSACTION
        RETURN 1
    END

    COMMIT TRANSACTION
    RETURN 0

---------------------------------------------------------------------
-- 6.4 TRY...CATCH Xe[gg
---------------------------------------------------------------------

CREATE PROCEDURE dbo.ap_ChargeLog_Insert
    @ItemId INT,
    @ActionId SMALLINT,
    @Cost MONEY,
    @Note VARCHAR(max),
    @Activity VARCHAR(1000)
AS
    BEGIN TRY
        INSERT ChargeLog(
            ItemId,
            ActionId,
            ChargeDate,
            Cost,
            Note
        )
        VALUES(
            @ItemId,
            @ActionId,
            GETDATE(),
            @Cost,
            @Note
        )

        INSERT INTO ActivityLog(
            Activity,
            LogDate,
            UserName,
            Note
        )
        VALUES(
            @Activity,
            GETDATE(),
            SYSTEM_USER,
            @Note
        )
    END TRY

    BEGIN CATCH
        INSERT INTO ErrorLog(
            ErrorNum,
            ErrorType,
            ErrorMsg,
            ErrorSource
        )
        VALUES(
            50000,
            'E',
            'Unable to record transaction in ChargeLog.',
            'ap_ChargeLog_Insert'
        )
    END CATCH

    RETURN


BEGIN TRY
    PRINT N'TRYubNJn'
    INSERT ChargeLog(ItemId, ActionId, ChargeDate, Cost, Note)
    VALUES(30, 15, GETDATE(), $150, NULL)

    RAISERROR(N'G[܂!', 10, 1)

    INSERT INTO ActivityLog(Activity, LogDate, UserName, Note)
    VALUES('Repair', GETDATE(), SYSTEM_USER, NULL)
    PRINT N'TRYubNI'
END TRY

BEGIN CATCH
    PRINT N'CATCHubN'
    INSERT INTO ErrorLog(ErrorNum, ErrorType, ErrorMsg, ErrorSource)
    VALUES(50000, 'E', 'Unable to record transaction in ChargeLog.',
        'ap_ChargeLog_Insert')
END CATCH

PRINT N'܂!'


BEGIN TRY
    PRINT N'TRYubNJn'
    RAISERROR(N'G[܂!', 23, 1)
    PRINT N'TRYubNI'
END TRY

BEGIN CATCH
    PRINT N'CATCHubN'
END CATCH

PRINT N'܂!'


PRINT N'Jn'
RAISERROR(N'G[܂!', 23, 1) WITH LOG
IF @@ERROR <> 0
    PRINT N'G[o܂'


DECLARE @i INT
PRINT N'Jn'
SET @i = '2005/2/2'
IF @@ERROR <> 0
    PRINT N'G[܂!'
PRINT N'܂!'


BEGIN TRY
    PRINT N'TRYubNJn'
    DECLARE @i INT
    SET @i = '2005/2/2'
    PRINT N'TRYubNI'
END TRY

BEGIN CATCH
    PRINT N'CATCHubN'
END CATCH
PRINT N'܂!'


CREATE Procedure dbo.ap_ChargeLog_Insert2
    @ItemId INT,
    @ActionId SMALLINT,
    @Cost MONEY,
    @Note VARCHAR(max),
    @Activity VARCHAR(1000)
AS
    BEGIN TRY
        INSERT ChargeLog(
            ItemId,
            ActionId,
            ChargeDate,
            Cost,
            Note
        )
        VALUES(
            @ItemId,
            @ActionId,
            GETDATE(),
            @Cost,
            @Note
        )

        INSERT INTO ActivityLog(
            Activity,
            LogDate,
            UserName,
            Note
        )
        VALUES(
            @Activity,
            GETDATE(),
            SYSTEM_USER,
            @Note
        )
    END TRY

    BEGIN CATCH
        DECLARE @severity INT
        SET @severity = ERROR_SEVERITY()
        DECLARE @msg NVARCHAR(255)
        SET @msg = N'Unable to record transaction in ChargeLog.'
                 + N' Error(' + CAST(ERROR_NUMBER() AS NVARCHAR)
                 + N'):' + CAST(ERROR_MESSAGE() AS NVARCHAR)
                 + N' Severity = ' + CAST(ERROR_SEVERITY() AS NVARCHAR)
                 + N' State = ' + CAST(ERROR_STATE() AS NVARCHAR)
                 + N' Procedure = ' + CAST(ERROR_PROCEDURE() AS NVARCHAR)
                 + N' Line num. = ' + CAST(ERROR_LINE() AS NVARCHAR)

        INSERT INTO ErrorLog(
            ErrorNum,
            ErrorType,
            ErrorMsg,
            ErrorSource
        )
        VALUES(
            ERROR_NUMBER(),
            'E',
            @msg,
            ERROR_PROCEDURE()
        )

        RAISERROR (@msg, @severity, 2)
    END CATCH

    RETURN


CREATE PROCEDURE dbo.ap_ChargeLog_Insert_wTran
    @ItemId INT,
    @ActionId SMALLINT,
    @Cost MONEY,
    @Note VARCHAR(max),
    @Activity VARCHAR(1000)
AS
    BEGIN TRY
        BEGIN TRAN
        INSERT ChargeLog(
            ItemId,
            ActionId,
            ChargeDate,
            Cost,
            Note
        )
        VALUES(
            @ItemId,
            @ActionId,
            GETDATE(),
            @Cost,
            @Note
        )

        INSERT INTO ActivityLog(
            Activity,
            LogDate,
            UserName,
            Note
        )
        VALUES(
            @Activity,
            GETDATE(),
            SYSTEM_USER,
            @Note
        )
        COMMIT TRAN
    END TRY

    BEGIN CATCH
        ROLLBACK TRAN
        INSERT ErrorLog(
            ErrorNum,
            ErrorType,
            ErrorMsg,
            ErrorSource
        )
        VALUES(
            50000,
            'E',
            'Unable to record transaction in ChargeLog.',
            ERROR_PROCEDURE()
        )
    END CATCH

    RETURN


CREATE PROCEDURE dbo.ap_ChargeLog_Insert_wTranState
    @ItemId INT,
    @ActionId SMALLINT,
    @Cost MONEY,
    @Note VARCHAR(max),
    @Activity VARCHAR(1000)
AS
    DECLARE @Today SMALLDATETIME
    DECLARE @User SYSNAME
    DECLARE @ErrorCode INT
    DECLARE @EqId INT
    DECLARE @Price MONEY

    BEGIN TRY
        SELECT @Today = GETDATE()
        SET @User = SYSTEM_USER
        SET @ErrorCode = 0

        BEGIN TRAN
        INSERT ChargeLog(ItemId, ActionId, ChargeDate, Cost, Note)
        VALUES(@ItemId, @ActionId, @Today, @Cost, @Note)

        SELECT @EqId = EqId FROM dbo.OrderItem
        WHERE ItemId = @ItemId

        SELECT @Price = Price FROM dbo.PriceList
        WHERE EqId = @EqId

        INSERT INTO Sales(EqId, UnitPrice, Qty, ExtPrice, SalesDate)
        VALUES(@EqId, @Price, 1, @Price, @today)

        INSERT INTO ActivityLog(Activity, LogDate, UserName, Note)
        VALUES(@Activity, @Today , @User, @Note)

        COMMIT TRAN
    END TRY

    BEGIN CATCH
        SET @ErrorCode = ERROR_NUMBER()

        IF XACT_STATE() = -1
        -- gUNV̓R~bgłȂ
        BEGIN
            ROLLBACK TRAN
            INSERT ErrorLog(
                ErrorNum,
                ErrorType,
                ErrorMsg,
                ErrorSource,
                ErrorState
            )
            VALUES(
                @ErrorCode,
                'E',
                'Unable to commit transaction.',
                ERROR_PROCEDURE(),
                -1
            )
        END

        ELSE IF XACT_STATE() = 0
        --gUNVJnOɃG[
        BEGIN
            INSERT ErrorLog(
                ErrorNum,
                ErrorType,
                ErrorMsg,
                ErrorSource,
                ErrorState
            )
            VALUES(
                @ErrorCode,
                'E',
                'Unable to pre-process transaction.',
                ERROR_PROCEDURE(),
                0
            )
        END

        ELSE IF XACT_STATE() = 1
        -- R~bgƃ[obN̂\
        BEGIN
            COMMIT TRAN
            IF NOT EXISTS(SELECT * FROM dbo.ChargeLog
                      WHERE ItemId = @ItemId
                      AND ChargeDate = @Today)
            BEGIN
                INSERT ErrorLog(
                    ErrorNum,
                    ErrorType,
                    ErrorMsg,
                    ErrorSource,
                    ErrorState
                )
                VALUES(
                    @ErrorCode,
                    'E',
                    'Unable to record transaction in ChargeLog.',
                    ERROR_PROCEDURE(),
                    1
                )
            END

            IF NOT EXISTS(SELECT * FROM dbo.Sales
                      WHERE EqId = @EqId
                      AND SalesDate = @Today)
            BEGIN
                INSERT ErrorLog(
                    ErrorNum,
                    ErrorType,
                    ErrorMsg,
                    ErrorSource,
                    ErrorState
                )
                VALUES(
                    @ErrorCode,
                    'E',
                    'Unable to record transaction in Sales.',
                    ERROR_PROCEDURE(),
                    1
                )
            END

            IF NOT EXISTS(SELECT * FROM dbo.ActivityLog
                      WHERE Activity = @Activity
                      AND LogDate = @Today
                      AND UserName = @User)
            BEGIN
                INSERT ErrorLog(
                    ErrorNum,
                    ErrorType,
                    ErrorMsg,
                    ErrorSource,
                    ErrorState
                )
                VALUES(
                    @ErrorCode,
                    'E',
                    'Unable to record transaction in ActivityLog.',
                    ERROR_PROCEDURE(),
                    1
                )
            END
        END
    END CATCH

    RETURN @ErrorCode


CREATE PROCEDURE dbo.ap_SalesByEqId_IncreasePrice
    @EqId INT,
    @Factor REAL
AS
    SET XACT_ABORT ON

    BEGIN TRAN
    UPDATE dbo.Sales
    SET UnitPrice = UnitPrice * @Factor,
        ExtPrice = ExtPrice * @Factor
    WHERE EqId = @EqId

    WAITFOR DELAY '0:00:10'

    UPDATE dbo.PriceList
    SET Price = Price * @Factor
    WHERE EqId = @EqId
    COMMIT TRAN
    RETURN

GO

CREATE PROCEDURE dbo.ap_PriceByEqId_Set
    @EqId INT,
    @Price MONEY
AS
    SET XACT_ABORT ON

    BEGIN TRAN
    UPDATE dbo.PriceList
    SET Price = @Price
    WHERE EqId = @EqId

    WAITFOR DELAY '0:00:10'

    UPDATE dbo.Sales
    SET UnitPrice = @Price,
        ExtPrice = @Price * Qty
    WHERE EqId = @EqId

    COMMIT TRAN
    RETURN


CREATE PROCEDURE dbo.ap_PriceByEqId_Set_wRetry
    @EqId INT,
    @Price MONEY
AS
    DECLARE @i INT
    SET @i = 1
    WHILE @i <= 10
    BEGIN
        BEGIN TRY
            SET XACT_ABORT ON

            BEGIN TRAN
            UPDATE dbo.PriceList
            SET Price = @Price
            WHERE EqId = @EqId

            WAITFOR DELAY '0:00:10'

            UPDATE dbo.Sales
            SET UnitPrice = @Price,
                ExtPrice = @Price * Qty
            WHERE EqId = @EqId
            COMMIT TRAN

            PRINT 'Completed!'
            BREAK
        END TRY

        BEGIN CATCH
            IF ERROR_NUMBER() = 1205
            BEGIN
                ROLLBACK TRAN
                SET @i = @i + 1
                PRINT 'retry'

                INSERT INTO ErrorLog(
                    ErrorNum,
                    ErrorType,
                    ErrorMsg,
                    ErrorSource,
                    CreatedBy,
                    CreateDT,
                    ErrorState
                )
                VALUES(
                    ERROR_NUMBER(),
                    'E',
                    ERROR_MESSAGE(),
                    ERROR_PROCEDURE(),
                    SUSER_SNAME(),
                    GETDATE(),
                    ERROR_STATE()
                )

                WAITFOR DELAY '0:00:03'
            END
        END CATCH
    END
    PRINT 'Completed!'
    RETURN

---------------------------------------------------------------------
-- 6.5 SET XACT_ABORT
---------------------------------------------------------------------

CREATE TABLE t_test(i INT)
GO

SET XACT_ABORT ON

BEGIN TRAN
PRINT N'Jn'
INSERT t_test(i) VALUES(1)
INSERT t_test(i) VALUES('2007/5/5')
COMMIT TRAN
PRINT N'I'
GO

SELECT * FROM t_test


SET XACT_ABORT ON
BEGIN TRY
    BEGIN TRAN
    PRINT N'TRYubNJn'
    INSERT t_test(i) VALUES(1)
    INSERT t_test(i) VALUES('2007/5/5')
    COMMIT TRAN
    PRINT N'TRYubNI'
END TRY

BEGIN CATCH
    ROLLBACK TRAN
    PRINT N'CATCHubN'
END CATCH

SELECT * FROM t_test


CREATE PROCEDURE dbo.ap_LeasedAsset_Insert7
-- [Xi}A[X̑zXV
-- iSET XACT_ABORT ONg\[V̗j
(
    @intEqId INT,
    @intLocationId INT,
    @intStatusId INT,
    @intLeaseId INT,
    @intLeaseScheduleId INT,
    @intOwnerId INT,
    @mnyLease MONEY,
    @intAcquisitionTypeID INT
)
AS
    SET NOCOUNT ON
    SET XACT_ABORT ON

    BEGIN TRANSACTION
    -- ȋ}
    INSERT Inventory(
        EqId,
        LocationId,
        StatusId,
        LeaseId,
        LeaseScheduleId,
        OwnerId,
        Lease,
        AcquisitionTypeID
    )
    VALUES(
        @intEqId,
        @intLocationId,
        @intStatusId,
        @intLeaseId,
        @intLeaseScheduleId,
        @intOwnerId,
        @mnyLease,
        @intAcquisitionTypeID
    )

    -- [X̑z̍XV
    UPDATE dbo.LeaseSchedule
    SET PeriodicTotalAmount = PeriodicTotalAmount + @mnyLease
    WHERE LeaseId = @intLeaseId
    COMMIT TRANSACTION

    RETURN (0)


CREATE PROCEDURE dbo.ap_Equipment_Insert
-- ĩR[h}iKvɉĔi̎ނ}j
-- iG[уgUNV@̑֗j
    @chvMake VARCHAR(50),
    @chvModel VARCHAR(50),
    @chvEqType VARCHAR(50),
    @intEqId INT OUTPUT
AS
    SET NOCOUNT ON
    SET XACT_ABORT ON
    DECLARE @intTrancountOnEntry INT,
            @intEqTypeId INT

    SET @intTrancountOnEntry = @@TRANCOUNT

    -- EqTypee[uɎw̔i̎ށiEqTypej݂Ȃ΁A}
    IF NOT EXISTS (SELECT EqTypeId FROM EqType
                   WHERE EqType = @chvEqType)
    BEGIN
        IF @@TRANCOUNT = 0
            BEGIN TRAN

        INSERT EqType(EqType)
        VALUES(@chvEqType)
        -- }EqTypeID擾
        SELECT @intEqTypeId = @@IDENTITY
    END
    ELSE
    BEGIN
        -- wEqTypeID擾
        SELECT @intEqTypeId = EqTypeId
        FROM dbo.EqType
        WHERE EqType = @chvEqType
    END

    -- i}
    INSERT dbo.Equipment(Make, Model, EqTypeId)
    VALUES(@chvMake, @chvModel, @intEqTypeId)

    SELECT @intEqId = @@IDENTITY

    IF @@TRANCOUNT > @intTrancountOnEntry
        COMMIT TRAN
    RETURN 0


CREATE PROCEDURE dbo.ap_Inventory_InsertXA
-- iR[h}ĂIDԂAInventoryCounte[uXV
-- iG[уgUNV@̑֗j
    @intEqId INT,
    @intLocationId INT,
    @inyStatusId TINYINT,
    @intLeaseId INT,
    @intLeaseScheduleId INT,
    @intOwnerId INT,
    @mnsRent SMALLMONEY,
    @mnsLease SMALLMONEY,
    @mnsCost SMALLMONEY,
    @inyAcquisitionTypeID INT,
    @intInventoryId INT OUTPUT
AS
    SET NOCOUNT ON
    SET XACT_ABORT ON
    DECLARE @intTrancountOnEntry INT
    SET @intTrancountOnEntry = @@TRANCOUNT

    IF @@TRANCOUNT = 0
        BEGIN TRAN

    INSERT INTO dbo.Inventory(
        EqId,
        LocationId,
        StatusId,
        LeaseId,
        LeaseScheduleId,
        OwnerId,
        Rent,
        Lease,
        Cost,
        AcquisitionTypeID
    )
    VALUES(
        @intEqId,
        @intLocationId,
        @inyStatusId,
        @intLeaseId,
        @intLeaseScheduleId,
        @intOwnerId,
        @mnsRent,
        @mnsLease,
        @mnsCost,
        @inyAcquisitionTypeID
    )

    SELECT @intInventoryId = SCOPE_IDENTITY()

    UPDATE InventoryCount
    SET InvCount = InvCount + 1
    WHERE LocationId = @intLocationId

    IF @@ROWCOUNT <> 1
    BEGIN
        -- rWlXWbÑG[
        RAISERROR(50133, 16, 1)
        IF @@TRANCOUNT > @intTrancountOnEntry
            ROLLBACK TRAN
        RETURN 50133
    END

    IF @@TRANCOUNT > @intTrancountOnEntry
        COMMIT TRAN
    RETURN 0


CREATE PROCEDURE dbo.ap_InventoryEquipment_Insert_XA
-- VK̍݌ɂєĩR[h}
-- iG[уgUNV@̑֗j
    @chvMake VARCHAR(50),
    @chvModel VARCHAR(50),
    @chvEqType VARCHAR(30),
    @intLocationId INT,
    @inyStatusId TINYINT,
    @intLeaseId INT,
    @intLeaseScheduleId INT,
    @intOwnerId INT,
    @mnsRent SMALLMONEY,
    @mnsLease SMALLMONEY,
    @mnsCost SMALLMONEY,
    @inyAcquisitionTypeID INT,
    @intInventoryId INT OUTPUT,
    @intEqId INT OUTPUT
AS
    SET NOCOUNT ON
    SET XACT_ABORT ON

    DECLARE @intError INT,
            @intTrancountOnEntry INT

    SET @intError = 0
    SET @intTrancountOnEntry = @@TRANCOUNT

    IF @@TRANCOUNT = 0
        BEGIN TRAN

    -- f[^x[XɊɑ݂i
    IF NOT EXISTS(SELECT EqId
                  FROM Equipment
                  WHERE Make = @chvMake
                  AND Model = @chvModel)
        EXEC @intError = dbo.ap_Equipment_Insert
                            @chvMake,
                            @chvModel,
                            @chvEqType,
                            @intEqId OUTPUT

    IF @intError > 0
    BEGIN
        IF @@TRANCOUNT > @intTrancountOnEntry
            ROLLBACK TRAN
        RETURN @intError
    END

    EXEC @intError = dbo.ap_Inventory_InsertXA
                        @intEqId,
                        @intLocationId,
                        @inyStatusId,
                        @intLeaseId,
                        @intLeaseScheduleId,
                        @intOwnerId,
                        @mnsRent,
                        @mnsLease,
                        @mnsCost,
                        @inyAcquisitionTypeID,
                        @intInventoryId OUTPUT

    IF @intError > 0
    BEGIN
        IF @@TRANCOUNT > @intTrancountOnEntry
            ROLLBACK TRAN
        RETURN @intError
    END

    IF @@TRANCOUNT > @intTrancountOnEntry
        COMMIT TRAN
    RETURN 0


DECLARE @intError INT,
        @intInvId INT,
        @intEqId INT
BEGIN TRAN

EXEC @intError = ap_InventoryEquipment_Insert_XA
     @chvMake = 'Compaq',
     @chvModel = 'IPaq 3835',
     @chvEqType = 'PDA',
     @intLocationId = 12,
     @inyStatusId = 1,
     @intLeaseId = null,
     @intLeaseScheduleId = 1,
     @intOwnerId = 411,
     @mnsRent = null,
     @mnsLease = null,
     @mnsCost = $650,
     @inyAcquisitionTypeID = 1,
     @intInventoryId = @intInvId OUTPUT,
     @intEqId = @intEqId OUTPUT

IF @intError = 0
    COMMIT TRAN
ELSE
    ROLLBACK TRAN

SELECT @intError Err
SELECT * FROM Inventory WHERE InventoryId = @intInvId
SELECT * FROM Equipment WHERE EqId = @intEqId

