HumanResources.uspUpdateEmployeeHireInfo Stored Procedure

Description

Updates the Employee table and inserts a new row in the EmployeePayHistory table with the values specified in the input parameters.

Properties

Creation Date	27/10/2017 14:33
Encrypted
Ansi Nulls

Parameters

Parameter	Direction	Description	Data Type	Size
@BusinessEntityID	In	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a valid BusinessEntityID from the Employee table.	Integer	4
@JobTitle	In	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a title for the employee.	VarWChar	50
@HireDate	In	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a hire date for the employee.	DBTimeStamp	4
@RateChangeDate	In	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter the date the rate changed for the employee.	DBTimeStamp	4
@Rate	In	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter the new rate for the employee.	Currency	8
@PayFrequency	In	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter the pay frequency for the employee.	UnsignedTinyInt	1
@CurrentFlag	In	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter the current flag for the employee.	dbo.Flag	1
@RETURN_VALUE	Return Value		Integer	4

Objects that HumanResources.uspUpdateEmployeeHireInfo depends on

Database Object	Object Type	Description	Dep Level
Person.BusinessEntity	Table	Source of the ID that connects vendors, customers, and employees with address and contact information.	3
HumanResources.Employee	Table	Employee information such as salary, department, and title.	1
HumanResources.EmployeePayHistory	Table	Employee pay history.	1
dbo.ErrorLog	Table	Audit table tracking errors in the the AdventureWorks database that are caught by the CATCH block of a TRY...CATCH construct. Data is inserted by stored procedure dbo.uspLogError when it is executed from inside the CATCH block of a TRY...CATCH construct.	2
dbo.Flag	User Defined Data Type		1
dbo.Name	User Defined Data Type		3
dbo.NameStyle	User Defined Data Type		3
Person.Person	Table	Human beings involved with AdventureWorks: employees, customer contacts, and vendor contacts.	2
dbo.uspLogError	Stored Procedure	Logs error information in the ErrorLog table about the error that caused execution to jump to the CATCH block of a TRY...CATCH construct. Should be executed from within the scope of a CATCH block otherwise it will return without inserting error information.	1
dbo.uspPrintError	Stored Procedure	Prints error information about the error that caused execution to jump to the CATCH block of a TRY...CATCH construct. Should be executed from within the scope of a CATCH block otherwise it will return without printing any error information.	2

Procedure Source Code

CREATE PROCEDURE [HumanResources].[uspUpdateEmployeeHireInfo]
    @BusinessEntityID [int], 
    @JobTitle [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 [JobTitle] = @JobTitle 
            ,[HireDate] = @HireDate 
            ,[CurrentFlag] = @CurrentFlag 
        WHERE [BusinessEntityID] = @BusinessEntityID;

        INSERT INTO [HumanResources].[EmployeePayHistory] 
            ([BusinessEntityID]
            ,[RateChangeDate]
            ,[Rate]
            ,[PayFrequency]) 
        VALUES (@BusinessEntityID, @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;

Related Objects