-- Drop stored procedure if it already exists

IF EXISTS (

  SELECT * 

    FROM INFORMATION_SCHEMA.ROUTINES 

   WHERE SPECIFIC_SCHEMA = N'dbo'

     AND SPECIFIC_NAME = N'sp_EmployeeMaster_MERGE' 

)

   DROP PROCEDURE dbo.sp_EmployeeMaster_MERGE

GO

CREATE PROCEDURE dbo.sp_EmployeeMaster_MERGE

@FileNumber int,  

@LastName varchar(25),  

@FirstName varchar(25),  

@PositionNumber int  ,

@UserID varchar(255)

AS

MERGE EmployeeMaster AS t

    USING (SELECT @filenumber as filenumber, @lastname as lastname, 

@firstname as firstname, @positionnumber as positionnumber ) AS s

        ON (t.filenumber = s.filenumber)

    WHEN MATCHED AND (t.firstname <> s.firstname or t.lastname <> s.lastname 

or t.positionnumber <> s.positionnumber)

        THEN UPDATE SET t.firstname = s.firstname , t.lastname = s.lastname , 

t.positionnumber = s.positionnumber,

                    t.ModifiedDateTime = GETDATE(),

                    t.ModifiedUser = @UserID

    when not matched then

        INSERT      (FileNumber  ,LastName  ,FirstName  ,PositionNumber , modifiedUser)  

            VALUES  (@FileNumber ,@LastName ,@FirstName ,@PositionNumber, @UserID) 

     OUTPUT $action, Inserted.*, deleted.*;

GO

grant exec on sp_EmployeeMaster_MERGE to public