在sql中执行SP时传递Readonly数据类型的数据

CREATE TYPE dbo.EmployeeType AS TABLE
(
    EmployeeID INT,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Department NVARCHAR(50)
);

CREATE PROCEDURE dbo.InsertEmployees
    @Employees dbo.EmployeeType READONLY
AS
BEGIN
    INSERT INTO Employees (EmployeeID, FirstName, LastName, Department)
    SELECT EmployeeID, FirstName, LastName, Department
    FROM @Employees;
END;

DECLARE @empTable dbo.EmployeeType;

INSERT INTO @empTable (EmployeeID, FirstName, LastName, Department)
VALUES (1, 'John', 'Doe', 'HR'),
       (2, 'Jane', 'Smith', 'Engineering');

EXEC dbo.InsertEmployees @Employees = @empTable;

-- 确保@empTable已被正确填充
IF EXISTS (SELECT * FROM @empTable)
BEGIN
    EXEC dbo.InsertEmployees @Employees = @empTable;
END
ELSE
BEGIN
    PRINT 'No data to insert.';
END

-- 错误的尝试：直接修改Readonly参数
-- 正确的做法：复制到临时表后再进行修改
CREATE TABLE #TempEmployees AS SELECT * FROM @Employees;

-- 现在可以对#TempEmployees进行修改操作
UPDATE #TempEmployees SET Department = 'NewDepartment' WHERE EmployeeID = 1;

-- 最后使用修改后的数据进行插入或其他操作
INSERT INTO Employees (EmployeeID, FirstName, LastName, Department)
SELECT EmployeeID, FirstName, LastName, Department FROM #TempEmployees;