Properties

Owner:	HumanResources
Encrypted:
Creation Date:	04/26/2006
Modification Date:
Description:	Updates the Employee table and inserts a new row in the EmployeePayHistory table with the values specified in the input parameters.

Creation options

QUOTED_IDENTIFIER:
ANSI_NULLS:

Parameters

	Name	Direction	DataType	Length	Default	Description
	@EmployeeID	INPUT	int	4		Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a valid EmployeeID from the Employee table.
	@Title	INPUT	nvarchar	50		Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a title for the employee.
	@HireDate	INPUT	datetime	8		Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a hire date for the employee.
	@RateChangeDate	INPUT	datetime	8		Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter the date the rate changed for the employee.
	@Rate	INPUT	money	8		Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter the new rate for the employee.
	@PayFrequency	INPUT	tinyint	1		Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter the pay frequency for the employee.
	@CurrentFlag	INPUT	Flag	1		Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter the current flag for the employee.

Total: 7 parameter(s)

Objects that [HumanResources].[uspUpdateEmployeeHireInfo] depends on

	Object Name	Owner	Object Type	Dep Level
	Flag	dbo	User Defined type	1
	Name	dbo	User Defined type	1
	NameStyle	dbo	User Defined type	1
	Phone	dbo	User Defined type	1
	Contact	Person	Table	2
	ErrorLog	dbo	Table	2
	uspPrintError	dbo	Procedure	2
	Employee	HumanResources	Table	3
	uspLogError	dbo	Procedure	3
	EmployeePayHistory	HumanResources	Table	4

Total: 10 object(s)

SQL

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [HumanResources].[uspUpdateEmployeeHireInfo]
    @EmployeeID [int],
    @Title [nvarchar](50),
    @HireDate [datetime],
    @RateChangeDate [datetime],
    @Rate [money],
    @PayFrequency [tinyint],
    @CurrentFlag [dbo].[Flag]
WITH EXECUTE AS CALLER
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        UPDATE [HumanResources].[Employee]
        SET [Title] = @Title
            ,[HireDate] = @HireDate
            ,[CurrentFlag] = @CurrentFlag
        WHERE [EmployeeID] = @EmployeeID;

        INSERT INTO [HumanResources].[EmployeePayHistory]
            ([EmployeeID]
            ,[RateChangeDate]
            ,[Rate]
            ,[PayFrequency])
        VALUES (@EmployeeID, @RateChangeDate, @Rate, @PayFrequency);

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        -- Rollback any active or uncommittable transactions before
        -- inserting information in the ErrorLog
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END

        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;

GO
SET QUOTED_IDENTIFIER OFF
GO

GO
SET ANSI_NULLS OFF
GO

See Also

List of stored procedures