时间戳

首页 > 解决方案 > 如何将存储过程的结果放在视图的新列中？

sql - 如何将存储过程的结果放在视图的新列中？

问题描述

id我创建了一个存储过程，它将模式名称和表名称作为输入，并输出一个包含表列中最大值的 INT 。我还创建了一个视图，其中包含数据库中的所有模式名称、表名称和主键列名称。我想在此视图中添加一列，其中包含我的存储过程的结果，使用模式名称和表名称值作为行的存储过程的输入。我尝试了一些不同的东西，但我找不到办法。

存储过程：

CREATE PROCEDURE [dbo].[sp_ttw_max_id]
(
    @schema VARCHAR(50),
    @table VARCHAR(50),
    @max_id INT OUTPUT
) AS
BEGIN
    -- Another custom stored procedure to help sanitize the input
    EXEC sp_ttw_santize @schema, @table, @schema OUTPUT, @table OUTPUT;

    DECLARE @param NVARCHAR(255); 
    SET @param = '@max_id_param INT OUTPUT';

    DECLARE @sql NVARCHAR(255); 
    SET @sql = 'SELECT TOP 1 @max_id_param = [id]
                FROM ' + @table + '
                ORDER BY id DESC;'

    EXEC sp_executesql @sql, @param, @max_id_param = @max_id OUTPUT;
END;

看法：

CREATE VIEW [dbo].[pk_columns] 
AS
    SELECT 
        col_cnst.TABLE_SCHEMA, col_cnst.TABLE_NAME, COLUMN_NAME
    FROM
        INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE col_cnst
    JOIN 
        INFORMATION_SCHEMA.TABLE_CONSTRAINTS tbl_cnst ON col_cnst.CONSTRAINT_NAME = tbl_cnst.CONSTRAINT_NAME
    WHERE 
        CONSTRAINT_TYPE = 'PRIMARY KEY';

我的尝试

首先，我尝试添加@max_id到视图SELECT语句中的列列表：

DECLARE @max_id INT;

SELECT col_cnst.TABLE_SCHEMA, col_cnst.TABLE_NAME, COLUMN_NAME, @max_id...

这在我的视图中放置了一列空值。不幸的是，我不知道如何实际分配值。用我的语句替换参数名称EXEC没有帮助。

其次，我尝试将更改视图添加到临时表中并对DEFAULT我的新列施加约束，但我遇到了同样的问题。

第三，我尝试修改存储过程以不返回最大 ID 作为OUTPUT参数。@max_id我将之前尝试中的所有 s 都替换为EXECs，但仍然不行。

这甚至可能吗？

标签： sqlsql-serverdatabasetsqlstored-procedures

解决方案

您不能在视图中包含存储过程的输出。

但是，在这种情况下，您似乎可以通过不同的方式获取相同的数据。无需使用动态 SQL 直接查询表，您可以访问sys.identity_columns.

DECLARE @schema nvarchar(128),
    @table nvarchar(128)

SELECT SchemaName = s.name,
         TableName = o.name,
         LastValue = ic.last_value,
         IncrementValue = ic.increment_value
FROM sys.identity_columns AS ic
JOIN sys.objects AS o ON o.object_id = ic.object_id
JOIN sys.schemas AS s ON s.schema_id = o.schema_id
WHERE s.name = @schema
AND o.name = @table;

通过直接查询元数据，您现在可以通过使用适当的连接或子查询将查询合并到您的视图中。它还比直接查询表要快得多。在您的情况下，如果您只想要所有标识列的列表（假设每个 ID 都是主键），那么上述查询（删除了变量）似乎可以让您获得几乎想要获得的输出。

另请注意，模式和表名称可以包含 Unicode 字符，最长可达 128 个字符。您应该确保您的输入参数支持 Unicode ( Nvarchar)，并且足够长 (128)。

和有一个小的区别sys.identity_columns.last_value，MAX(schema.table.id)即可以使用 ID 值（通过插入），然后回滚或删除。这将导致表中的 ID 值出现差距，并且该差距在何时sys.identity_columns.last_value以及MAX(schema.table.id)不同步时都会反映出来。在任何前瞻性场景中，这last_value将是最正确的，但如果您专门回顾以确定“表中当前存在的最高承诺值”，那么您需要计算MAX()100% 确定返回的值存在。

推荐阅读