Procedure: [AdventureWorks2016CTP3].[HumanResources].[sp_UpdateEmployee_Temporal]

CollapseAll image

Collapse image Procedure properties


 Name   Value 
 Schema   [HumanResources] 
 Owner   [dbo] 
 Creation date   23.10.2015 
 Type   P 
 Encrypted   
 ID   1966630049 
 Implementation type   Transact SQL 
 Is native compiled   

Collapse image Creation options


Name Value
QUOTED_IDENTIFIER ON
ANSI_NULLS ON

Collapse image Parameters


Name  Description Datatype  Max length  Type  ReadOnly
@BusinessEntityID   int 4 Input  
@LoginID   nvarchar 256 Input  
@JobTitle   nvarchar 50 Input  
@MaritalStatus   nchar 1 Input  
@Gender   nchar 1 Input  
@VacationHours   smallint 2 Input  
@SickLeaveHours   smallint 2 Input  

Collapse image Objects that [HumanResources].[sp_UpdateEmployee_Temporal] depends on


Object name Object type Dep level
[HumanResources] Schema 1
[HumanResources].[Employee_Temporal] Table 1
Total 2 object(s)

Collapse image SQL


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

/*
    Stored procedure for updating columns of [HumanResources].[Employee_Temporal]
    If all parameters except @BusinessEntityID are NULL no update is performed 
    For NON NULL columns NULL values are ignored (i.e. existing values is applied)
*/
CREATE PROCEDURE [HumanResources].[sp_UpdateEmployee_Temporal]
 @BusinessEntityID INT
,@LoginID nvarchar(256) = NULL   
,@JobTitle nvarchar(50) = NULL
,@MaritalStatus nchar(1) = NULL
,@Gender nchar(1) = NULL
,@VacationHours smallint = 0
,@SickLeaveHours smallint = 0

AS
IF @LoginID IS NOT NULL OR @JobTitle IS NOT NULL OR @MaritalStatus IS NOT NULL 
OR @Gender IS NOT NULL OR @VacationHours IS NOT NULL OR @SickLeaveHours IS NOT NULL 

    UPDATE [HumanResources].[Employee_Temporal]
    SET  [LoginID] = ISNULL (@LoginID, LoginID),
    JobTitle = ISNULL (@JobTitle, JobTitle),
    MaritalStatus = ISNULL (@MaritalStatus, MaritalStatus),
    Gender = ISNULL (@Gender, Gender),
    VacationHours = ISNULL (@VacationHours, VacationHours),
    SickLeaveHours = ISNULL (@SickLeaveHours, SickLeaveHours)    
    WHERE BusinessEntityID = @BusinessEntityID;    
    
GO

Collapse image See also


List of procedures