如何将存储过程的返回结果作为新列添加到现有SQL视图中

根因分析 #

视图本质是预定义的静态SELECT查询，SQL Server 不允许在视图定义中声明变量、调用存储过程、执行动态SQL，因此你之前的尝试均无法生效。该需求可以通过以下两种方案实现：

方案1：动态SQL即席查询（适合临时查询场景） #

不需要修改原有存储过程和视图，直接执行以下动态SQL即可返回带MAX_ID列的结果集：

DECLARE @sql NVARCHAR(MAX) = N'';

-- 拼接所有表的最大值查询语句
SELECT @sql += N'UNION ALL
SELECT ''' + TABLE_SCHEMA + N''' AS TABLE_SCHEMA,
       ''' + TABLE_NAME + N''' AS TABLE_NAME,
       ''' + COLUMN_NAME + N''' AS COLUMN_NAME,
       MAX(' + QUOTENAME(COLUMN_NAME) + N') AS MAX_ID
FROM ' + QUOTENAME(TABLE_SCHEMA) + N'.' + QUOTENAME(TABLE_NAME) + N' '
FROM [dbo].[pk_columns]
-- 可选过滤：只保留主键为id且类型为INT的表
WHERE COLUMN_NAME = 'id'

-- 移除开头多余的UNION ALL
SET @sql = STUFF(@sql, 1, 10, N'');

-- 执行查询
EXEC sp_executesql @sql;

方案2：表值函数（适合需要像视图一样反复查询的场景） #

你可以创建多语句表值函数，内部遍历所有表调用原有存储过程计算最大值，后续可以像查询视图一样直接调用函数：

CREATE FUNCTION dbo.GetTableMaxId()
RETURNS @result TABLE (
    TABLE_SCHEMA VARCHAR(50),
    TABLE_NAME VARCHAR(50),
    COLUMN_NAME VARCHAR(50),
    MAX_ID INT
)
AS
BEGIN
    DECLARE 
        @schema VARCHAR(50), 
        @table VARCHAR(50), 
        @col VARCHAR(50), 
        @max_id INT;
    
    -- 声明游标遍历所有主键表
    DECLARE cur CURSOR FOR
    SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME 
    FROM dbo.pk_columns 
    WHERE COLUMN_NAME = 'id';
    
    OPEN cur;
    FETCH NEXT FROM cur INTO @schema, @table, @col;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 调用你已有的存储过程获取最大值
        EXEC dbo.sp_ttw_max_id @schema, @table, @max_id OUTPUT;
        -- 写入结果集
        INSERT INTO @result VALUES (@schema, @table, @col, @max_id);
        
        FETCH NEXT FROM cur INTO @schema, @table, @col;
    END
    
    CLOSE cur;
    DEALLOCATE cur;
    RETURN;
END;

创建完成后直接执行即可获取结果：

SELECT * FROM dbo.GetTableMaxId();

原有存储过程BUG修复 #

你当前的存储过程拼接SQL时没有拼接schema名，非dbo schema下的表会查询失败，同时建议使用QUOTENAME防注入，修改@sql赋值部分如下：

SET @sql = 'SELECT TOP 1 @max_id_param = [id]
            FROM ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + '
            ORDER BY id DESC;'

内容的提问来源于stack exchange，提问作者JDCAce