— 创建存储过程
CREATE PROCEDURE fgedu.process_employees
@employees dbo.EmployeeTableType READONLY
AS
BEGIN
— 计算部门平均工资
SELECT
department_id,
AVG(salary) AS avg_salary,
COUNT(*) AS employee_count
FROM @employees
GROUP BY department_id
ORDER BY avg_salary DESC;
END;

— 调用存储过程
DECLARE @employees dbo.EmployeeTableType;
INSERT INTO @employees (employee_id, employee_name, department_id, salary)
VALUES
(1, ‘张三’, 1, 5000),
(2, ‘李四’, 1, 6000),
(3, ‘王五’, 2, 7000),
(4, ‘赵六’, 2, 8000),
(5, ‘钱七’, 3, 9000);
EXEC fgedu.process_employees @employees;

— 步骤2：表变量与动态SQL
DECLARE @temp_sales TABLE (
sale_id INT,
product_id INT,
customer_id INT,
sale_date DATETIME,
amount DECIMAL(18,2)
);

INSERT INTO @temp_sales (sale_id, product_id, customer_id, sale_date, amount)
SELECT sale_id, product_id, customer_id, sale_date, amount
FROM fgedu.sales
WHERE sale_date >= ‘2025-01-01’;

— 使用动态SQL查询表变量
DECLARE @sql NVARCHAR(MAX);
SET @sql = ‘SELECT * FROM @temp_sales WHERE product_id = @product_id’;
EXEC sp_executesql @sql, N’@temp_sales dbo.SalesTableType READONLY, @product_id INT’, @temp_sales, @product_id = 1;

— 步骤3：表变量与事务
BEGIN TRY
BEGIN TRANSACTION;

— 操作表变量
DECLARE @temp_sales_transaction TABLE (
sale_id INT,
product_id INT,
amount DECIMAL(18,2)
);

INSERT INTO @temp_sales_transaction (sale_id, product_id, amount)
VALUES (1, 1, 1000), (2, 2, 2000);

— 操作实际表
UPDATE fgedu.sales SET amount = amount * 1.1 WHERE sale_id = 1;

— 故意出错
— SELECT 1/0;

COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
PRINT ‘事务回滚’;
END CATCH;

— 查看表变量数据（不受回滚影响）
SELECT * FROM @temp_sales_transaction;

执行结果：