mulesoftsql-uspUpdateEmployee.sql

CREATE PROC [dbo].[uspUpdateEmployee] 
	  @BusinessEntityID INT
	, @FirstName NVARCHAR(50) = NULL
	, @MiddleName NVARCHAR(50) = NULL
	, @LastName NVARCHAR(50) = NULL
	, @EmailAddress NVARCHAR(50) = NULL
	, @LoginId NVARCHAR(256) = NULL

AS
BEGIN
	DECLARE
	  @FirstName_Old NVARCHAR(50) = NULL
	, @MiddleName_Old NVARCHAR(50) = NULL
	, @LastName_Old NVARCHAR(50) = NULL
	, @EmailAddress_Old NVARCHAR(50) = NULL
	, @LoginId_Old NVARCHAR(256) = NULL

 -- Get current values for that Employee
	SELECT  @FirstName_Old = Person.FirstName
		, @MiddleName_Old = Person.MiddleName
		, @LastName_Old = Person.LastName
		, @EmailAddress_Old = EmailAddress.EmailAddress
		, @LoginId_Old = Employee.LoginID
	  FROM	Person.Person
	  JOIN	Person.EmailAddress		ON Person.BusinessEntityID = EmailAddress.BusinessEntityID
	  JOIN	HumanResources.Employee	ON Person.BusinessEntityID = Employee.BusinessEntityID
	WHERE	Person.Person.BusinessEntityID = @BusinessEntityID

--	When a parameter is NULL, overwrite it with the current value on the database. 
	SET @FirstName = ISNULL(@FirstName, @FirstName_Old)
	SET @MiddleName = ISNULL(@MiddleName, @MiddleName_Old)
	SET @LastName = ISNULL(@LastName, @LastName_Old)
	SET @EmailAddress = ISNULL(@EmailAddress, @EmailAddress_Old)
	SET @LoginId = ISNULL(@LoginId, @LoginId_Old)

--	Update 3 tables
	UPDATE	Person.Person
	SET	  FirstName = @FirstName
		, MiddleName = @MiddleName
		, LastName = @LastName
		, ModifiedDate = GETDATE()
	WHERE
		BusinessEntityID = @BusinessEntityID

	UPDATE	Person.EmailAddress
	SET	EmailAddress = @EmailAddress
	WHERE	BusinessEntityID = @BusinessEntityID

	UPDATE	HumanResources.Employee
	SET	LoginID = @LoginId
	WHERE	BusinessEntityID = @BusinessEntityID

END