Procedure: [AdventureWorks2016CTP3].[Person].[sp_UpdatePerson_Temporal]

CollapseAll image

Collapse image Procedure properties


 Name   Value 
 Schema   [Person] 
 Owner   [dbo] 
 Creation date   23.10.2015 
 Type   P 
 Encrypted   
 ID   1934629935 
 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  
@PersonType   nchar 2 Input  
@Title   nvarchar 8 Input  
@FirstName   nvarchar 50 Input  
@MiddleName   nvarchar 50 Input  
@LastName   nvarchar 50 Input  
@Suffix   nvarchar 10 Input  
@EmailPromotion   smallint 2 Input  

Collapse image Objects that [Person].[sp_UpdatePerson_Temporal] depends on


Object name Object type Dep level
[Person] Schema 1
[Person].[Person_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 Person_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 [Person].[sp_UpdatePerson_Temporal]
@BusinessEntityID INT,
@PersonType nchar(2) = NULL,
@Title nvarchar(8) = NULL,
@FirstName nvarchar(50) = NULL,
@MiddleName nvarchar(50) = NULL,
@LastName nvarchar(50) = NULL,
@Suffix nvarchar(10) = NULL,
@EmailPromotion smallint = NULL

AS

IF @PersonType IS NOT NULL OR @Title IS NOT NULL OR @FirstName IS NOT NULL OR @MiddleName IS NOT NULL
OR @LastName IS NOT NULL OR @Suffix IS NOT NULL OR @EmailPromotion IS NOT NULL 

    UPDATE Person.Person_Temporal
    SET PersonType = ISNULL (@PersonType, PersonType),
    Title = @Title,
    FirstName = ISNULL (@FirstName, FirstName),
    MiddleName = ISNULL (@MiddleName, MiddleName),
    LastName = ISNULL (@LastName, LastName),
    Suffix = @Suffix,
    EmailPromotion = ISNULL(@EmailPromotion, EmailPromotion)
    WHERE BusinessEntityID = @BusinessEntityID;
    
GO

Collapse image See also


List of procedures