Columns which i inserted that selected values from another table are null. why?

Question

I have insert values which is selected from another table and my own values into the table. In the Table, columns which I inserted my own values are inserted. but columns which I inserted that selected values from another table are null. Why?

I have inserted data into the "EmployeeDeleteLog" table, but in "EmployeeDeleteLog" table "IDdeleted" and "DeleteDate" columns has data. other all columns has "NULL" value.

1.Part 1

create table Person
(
    IdNo int primary key identity NOT NULL,
    FirstName varchar(50) ,
    LastName varchar(100),
    Gender char(1) NOT NULL,
    Nationality varchar(50),
    Email varchar(100) UNIQUE,
    Fax varchar(12) UNIQUE,
    PostBox varchar(6),
    Street varchar(60),
    City varchar(60),
    Province varchar(60),
    Country varchar(100) NOT NULL    
);

CREATE TABLE EmployeeDeleteLog(
    IDdeleted int,
    FirstName varchar(50) ,
    LastName varchar(100),
    DeleteDate date,
    Email varchar(100),
    Fax varchar(12),
    PostBox varchar(6),
    Street varchar(60),
    City varchar(60),
    Province varchar(60),
    Country varchar(100)
)

2.Part 2

CREATE FUNCTION [dbo].[countIDs] (@EmpID AS INT)
RETURNS INT
AS
BEGIN
    DECLARE @myCount AS INT

    SELECT @myCount = COUNT(IdNo)
    FROM Person
    WHERE IdNo = @EmpID

    RETURN @myCount
END

3.Part 3

CREATE PROCEDURE [dbo].[Employee_Delete](@EmpID int)
WITH EXECUTE AS CALLER
AS
BEGIN

    IF [dbo].[countIDs](@EmpID) <> 1
    BEGIN
        RAISERROR ('ID does not exist', 1, 1)
        RETURN 0
    END



        --check to see if update occured 
        --and return status
        IF @@ROWCOUNT = 1
            BEGIN
                INSERT INTO EmployeeDeleteLog(IDdeleted,
                                            FirstName,
                                            LastName,
                                            DeleteDate,
                                            Email,
                                            Fax,
                                            PostBox,
                                            Street,
                                            City,
                                            Province,
                                            Country)
            SELECT  IDdeleted = IdNo,
                    FirstName,
                    LastName,
                    DeleteDate = GETDATE(),
                    Email,
                    Fax,
                    PostBox,
                    Street,
                    City,
                    Province,
                    Country
            FROM Person 
            WHERE IdNo=@EmpID

            --Attempt Delete
                DELETE FROM [dbo].[Person]
                WHERE IdNo = @EmpID
                RETURN 1
            END

        ELSE 
            RETURN 0
    END


    GO

Answer 1

use output when deleting

https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql

Simple example:

DELETE FROM dbo.table1  
OUTPUT DELETED.* INTO @MyTableVar  
WHERE id = 4 

In your case, work with this, it may contain an error

    DELETE FROM [dbo].[Person]              

    OUTPUT 
     DELETED.IdNo,
        DELETED.FirstName,
        DELETED.LastName,
        DeleteDate = GETDATE(),
        DELETED.Email,
        DELETED.Fax,
        DELETED.PostBox,
        DELETED.Street,
        DELETED.City,
        DELETED.Province,
        DELETED.Country
INTO EmployeeDeleteLog(IDdeleted,
                                FirstName,
                                LastName,
                                DeleteDate,
                                Email,
                                Fax,
                                PostBox,
                                Street,
                                City,
                                Province,
                                Country)

     WHERE IdNo = @EmpID

Answer 2

I have changed Procedure simply like this:

CREATE PROCEDURE [dbo].[Employee_Delete](@EmpID int) AS BEGIN

IF [dbo].[countIDs](@EmpID) <> 1
BEGIN
    RAISERROR ('ID does not exist', 1, 1)
    RETURN 0
END



                INSERT INTO EmployeeDeleteLog(IDdeleted,
                                            FirstName,
                                            LastName,
                                            DeleteDate,
                                            Email,
                                            Fax,
                                            PostBox,
                                            Street,
                                            City,
                                            Province,
                                            Country)
            SELECT  IDdeleted = IdNo,
                    FirstName,
                    LastName,
                    DeleteDate = GETDATE(),
                    Email,
                    Fax,
                    PostBox,
                    Street,
                    City,
                    Province,
                    Country
            FROM Person 
            WHERE IdNo=@EmpID

    --Attempt Delete
                DELETE FROM [dbo].[Person]
                WHERE IdNo = @EmpID

END

GO

Finished!