时间戳

首页 > 解决方案 > TSQL 动态查询成视图

tsql - TSQL 动态查询成视图

问题描述

我正在尝试创建一个能够在任一数据库上运行的动态 sql 查询 - 并将其作为view返回。查询不是问题，那个问题已经解决了。我的问题是我不确定最终如何将其作为视图返回。我尝试将它用作存储过程和表值函数，但到目前为止这两种解决方案都存在问题。

存储过程版本：

CREATE PROCEDURE sp_GetPickSummaryTable
AS
BEGIN
    
    DECLARE @PickSummaryTable TABLE (
        COLUMN_A date,
        COLUMN_B nvarchar(16),
        COLUMN_C int
    )

    DECLARE @database nvarchar(64)
    DECLARE @log_database nvarchar(64)
    DECLARE @sql nvarchar(4000)
    
    IF(LEFT(DB_NAME(), 4) = 'log_')
    BEGIN
        SET @log_database = DB_NAME()
        SET @database = SUBSTRING(DB_NAME(), 5, LEN(DB_NAME()) - 4)
    END
    ELSE
    BEGIN
        SET @log_database = 'log_' + DB_NAME()
        SET @database = DB_NAME()
    END

    SET @sql = '
        SELECT c.COLUMN_A, C.COLUMN_B, pl.COLUMN_C
        FROM
            ' + @log_database + '..PICK_LOG pl
            LEFT OUTER JOIN ' + @database + '..[CUSTOM] c ON pl.CUSTOM_NUM = C.CUSTOM_NUM AND pl.COMPANY = c.COMPANY
        WHERE pl.[TIMESTAMP] > DATEADD(DAY, -60, GETDATE())
    UNION
        SELECT c.COLUMN_A, C.COLUMN_B, pl.COLUMN_C
        FROM
            ' + @database + '..PICK_LOG pl
            LEFT OUTER JOIN ' + @database + '..[CUSTOM] c ON PL.CUSTOM_NUM = C.CUSTOM_NUM AND pl.COMPANY = c.COMPANY
        WHERE pl.[TIMESTAMP] > DATEADD(DAY, -60, GETDATE())'

    INSERT INTO @PickSummaryTable ([DATE], CUSTOM_GROUP, CUSTOM_NUM, COMPANY, CUSTOM_DESC, LINES, QTY_SUF, DISTINCT_ORDERS)
    EXEC sp_executesql @sql 
    
    SELECT [DATE], CUSTOM_GROUP, CUSTOM_NUM, COMPANY, CUSTOM_DESC, LINES, QTY_SUF, DISTINCT_ORDERS
    FROM @PickSummaryTable
END

如果在“数据库”或“log_database”上运行，这将正确返回我想要作为视图返回的数据表。但我无法在视图中访问存储过程的结果。

我要创建的视图如下所示：

CREATE VIEW [dbo].[PICK_SUMMARY]
AS
WITH A AS (
    EXEC sp_GetPickSummaryTable
) 
SELECT * FROM A

表值函数版本：

CREATE FUNCTION fn_GetPickSummaryTable()
RETURNS @PickSummaryTable TABLE (
        COLUMN_A date,
        COLUMN_B nvarchar(16),
        COLUMN_C int
    )
AS
BEGIN
    DECLARE @database nvarchar(64)
    DECLARE @log_database nvarchar(64)
    DECLARE @sql nvarchar(4000)
    
    IF(LEFT(DB_NAME(), 4) = 'log_')
    BEGIN
        SET @log_database = DB_NAME()
        SET @database = SUBSTRING(DB_NAME(), 5, LEN(DB_NAME()) - 4)
    END
    ELSE
    BEGIN
        SET @log_database = 'log_' + DB_NAME()
        SET @database = DB_NAME()
    END

    SET @sql = '
        SELECT c.COLUMN_A, C.COLUMN_B, pl.COLUMN_C
        FROM
            ' + @log_database + '..PICK_LOG pl
            LEFT OUTER JOIN ' + @database + '..[CUSTOM] c ON pl.CUSTOM_NUM = C.CUSTOM_NUM AND pl.COMPANY = c.COMPANY
        WHERE pl.[TIMESTAMP] > DATEADD(DAY, -60, GETDATE())
    UNION
        SELECT c.COLUMN_A, C.COLUMN_B, pl.COLUMN_C
        FROM
            ' + @database + '..PICK_LOG pl
            LEFT OUTER JOIN ' + @database + '..[CUSTOM] c ON PL.CUSTOM_NUM = C.CUSTOM_NUM AND pl.COMPANY = c.COMPANY
        WHERE pl.[TIMESTAMP] > DATEADD(DAY, -60, GETDATE())'

    INSERT INTO @PickSummaryTable ([DATE], CUSTOM_GROUP, CUSTOM_NUM, COMPANY, CUSTOM_DESC, LINES, QTY_SUF, DISTINCT_ORDERS)
    EXEC sp_executesql @sql 
    
    RETURN
END
GO

但这会返回错误，因此我无法创建该函数。

Invalid use of a side-effecting operator 'INSERT EXEC' within a function.

标签： tsql

解决方案

视图和函数都不能做exec任何事情，因此您不能在视图中使用动态 sql（即使您尝试在各个级别的嵌套下隐藏动态）。

然而，一切都没有丢失！有一些聪明的方法可以做你想做的事。

一种方法是动态创建同义词，然后有一个使用这些同义词的静态视图。例如：

create or alter procedure redirect_synonyms (@todb sysname) as begin
    drop synonym if exists sys_tables;
    declare @sql varchar(200) = concat('create synonym sys_tables for ', quotename(@todb), '.sys.tables')
    exec(@sql)
end

exec redirect_synonyms 'msdb';

create or alter view cleverview as select * from sys_tables;

select * from cleverview;
exec redirect_synonyms 'master';
select * from cleverview;

推荐阅读