Difference between revisions of "HumanResources.uspUpdateEmployeeHireInfo (procedure)"

From dbscript Online Help
Jump to: navigation, search
(New page: == wikibot == {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- | '''procedure | HumanResources.uspUpdateEmployeeHireInfo |- |} <pre> CREATE PROCEDURE [Hu...)
 
 
(7 intermediate revisions by the same user not shown)
Line 1: Line 1:
 
== wikibot ==
 
== wikibot ==
 +
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- valign="top"
 +
| '''Procedure
 +
| HumanResources.uspUpdateEmployeeHireInfo
 +
|- valign="top"
 +
| '''Description
 +
| Updates the Employee table and inserts a new row in the EmployeePayHistory table with the values specified in the input parameters.
 +
|}
 +
 +
=== Source ===
 +
<pre>
 +
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;
 +
</pre>
 +
 +
=== References ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Dependency Type
 +
| '''Object Type
 +
| '''Referenced Object
 +
 +
|- valign="top"
 +
| Update
 +
| Table
 +
| [[HumanResources.Employee_(table)|HumanResources.Employee]]
 +
 +
|- valign="top"
 +
| Insert
 +
| Table
 +
| [[HumanResources.EmployeePayHistory_(table)|HumanResources.EmployeePayHistory]]
 +
 +
|- valign="top"
 +
| Execute
 +
| Procedure
 +
| [[dbo.uspLogError_(procedure)|dbo.uspLogError]]
 +
 +
|- valign="top"
 +
| Schema
 +
| Schema
 +
| [[HumanResources_(schema)|HumanResources]]
 +
 +
|}
 +
 +
 +
== automatically generated ==
  
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
Line 5: Line 92:
 
| '''procedure
 
| '''procedure
 
| HumanResources.uspUpdateEmployeeHireInfo
 
| HumanResources.uspUpdateEmployeeHireInfo
 +
|- valign="top"
 +
| '''Description
 +
| Updates the Employee table and inserts a new row in the EmployeePayHistory table with the values specified in the input parameters.
 
|-
 
|-
 
|}
 
|}
  
 
<pre>
 
<pre>
 +
 
CREATE PROCEDURE [HumanResources].[uspUpdateEmployeeHireInfo]
 
CREATE PROCEDURE [HumanResources].[uspUpdateEmployeeHireInfo]
 
     @EmployeeID [int],  
 
     @EmployeeID [int],  
Line 52: Line 143:
 
END;
 
END;
 
</pre>
 
</pre>
 +
 +
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Dependency Type
 +
| '''Object Type
 +
| '''Referenced Object
 +
 +
|-
 +
| Update
 +
| Table
 +
| [[HumanResources.Employee_(table)|HumanResources.Employee]]
 +
 +
|-
 +
| Insert
 +
| Table
 +
| [[HumanResources.EmployeePayHistory_(table)|HumanResources.EmployeePayHistory]]
 +
 +
|-
 +
| Execute
 +
| Procedure
 +
| [[dbo.uspLogError_(procedure)|dbo.uspLogError]]
 +
 +
|-
 +
| Schema
 +
| Schema
 +
| [[HumanResources_(schema)|HumanResources]]
 +
 +
|}

Latest revision as of 23:16, 23 June 2010

wikibot[edit]

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

Source[edit]

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;

References[edit]

Dependency Type Object Type Referenced Object
Update Table HumanResources.Employee
Insert Table HumanResources.EmployeePayHistory
Execute Procedure dbo.uspLogError
Schema Schema HumanResources


automatically generated[edit]

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

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;


Dependency Type Object Type Referenced Object
Update Table HumanResources.Employee
Insert Table HumanResources.EmployeePayHistory
Execute Procedure dbo.uspLogError
Schema Schema HumanResources
Retrieved from "http://dbshelp.devio.at/index.php?title=HumanResources.uspUpdateEmployeeHireInfo_(procedure)&oldid=2603"