HumanResources.uspUpdateEmployeeHireInfo

AdventureWorks

- AdventureWorks

Documentation	AdventureWorks
Schema	HumanResources
Name	uspUpdateEmployeeHireInfo
Module	Human Resources

Input/Output

	Mode	Name	Data type	Description
1	IN	BusinessEntityID	int	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a valid BusinessEntityID from the Employee table.
Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a valid BusinessEntityID from the Employee table.
2	IN	JobTitle	nvarchar(50)	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a title for the employee.
Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a title for the employee.
3	IN	HireDate	datetime	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a hire date for the employee.
Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a hire date for the employee.
4	IN	RateChangeDate	datetime	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter the date the rate changed for the employee.
Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter the date the rate changed for the employee.
5	IN	Rate	money	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter the new rate for the employee.
Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter the new rate for the employee.
6	IN	PayFrequency	tinyint	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter the pay frequency for the employee.
Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter the pay frequency for the employee.
7	IN	CurrentFlag	bit	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter the current flag for the employee.
Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter the current flag for the employee.

Uses

Name
HumanResources.uspUpdateEmployeeHireInfo
HumanResources.Employee
HumanResources.Employee
HumanResources.EmployeePayHistory
dbo.uspLogError
dbo.ErrorLog
dbo.uspPrintError

Script

CREATE PROCEDURE [HumanResources].[uspUpdateEmployeeHireInfo] @BusinessEntityID [int], @JobTitle [nvarchar](50), @HireDate [datetime], @RateChangeDate [datetime], @Rate [money], @PayFrequency [tinyint], @CurrentFlag [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 [uspLogError]; END CATCH; END;

CREATE PROCEDURE [HumanResources].[uspUpdateEmployeeHireInfo]
    @BusinessEntityID [int], 
    @JobTitle [nvarchar](50), 
    @HireDate [datetime], 
    @RateChangeDate [datetime], 
    @Rate [money], 
    @PayFrequency [tinyint], 
    @CurrentFlag [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 [uspLogError];
    END CATCH;
END;

Exported: 2019-02-04 23:13, Last imported: 2018-03-07 11:56