如何将复杂的Microsoft SQL查询转换为视图或表？

核心问题：原查询无法直接转为视图的原因 #

SQL Server视图不支持以下特性，但你的查询全部用到了：

• 变量声明（DECLARE）
• 临时表（#MaintenanceInfo）
• 条件分支逻辑（IF）
• 局部表变量（@HealthState、@ClientState）

方案1：创建带参数的表值函数（推荐，保留参数化能力） #

将原查询封装为表值函数，既保留原查询的参数过滤能力，又能像视图一样直接查询。

完整代码 #

CREATE FUNCTION dbo.DeviceUpdateStatus
(
    @UserSIDs NVARCHAR(10),
    @CollectionID NVARCHAR(10),
    @Locale INT,
    @Categories NVARCHAR(250),
    @Compliant INT,
    @Targeted INT,
    @Superseded INT,
    @ArticleID NVARCHAR(10),
    @ExcludeArticleIDs NVARCHAR(250)
)
RETURNS TABLE
AS
RETURN
(
    WITH HealthState AS (
        SELECT BitMask = 0, StateName = N'Healthy' UNION ALL
        SELECT 1, N'Unmanaged' UNION ALL
        SELECT 2, N'Inactive' UNION ALL
        SELECT 4, N'Health Evaluation Failed' UNION ALL
        SELECT 8, N'Pending Restart' UNION ALL
        SELECT 16, N'Update Scan Failed' UNION ALL
        SELECT 32, N'Update Scan Late' UNION ALL
        SELECT 64, N'No Maintenance Window' UNION ALL
        SELECT 128, N'Distant Maintenance Window' UNION ALL
        SELECT 256, N'Expired Maintenance Window'
    ),
    ClientState AS (
        SELECT BitMask = 0, StateName = N'No Reboot' UNION ALL
        SELECT 1, N'Configuration Manager' UNION ALL
        SELECT 2, N'File Rename' UNION ALL
        SELECT 4, N'Windows Update' UNION ALL
        SELECT 8, N'Add or Remove Feature'
    ),
    Maintenance_CTE AS (
        SELECT
            CollectionMembers.ResourceID,
            NextServiceWindow.NextServiceWindow,
            RowNumber = DENSE_RANK() OVER (PARTITION BY ResourceID ORDER BY NextServiceWindow.NextServiceWindow)
        FROM vSMS_ServiceWindow AS ServiceWindow
        JOIN fn_rbac_FullCollectionMembership(@UserSIDs) AS CollectionMembers 
            ON CollectionMembers.CollectionID = ServiceWindow.SiteID
        JOIN fn_rbac_Collection(@UserSIDs) AS Collections 
            ON Collections.CollectionID = CollectionMembers.CollectionID
            AND Collections.CollectionType = 2 -- 设备集合
        CROSS APPLY ufn_CM_GetNextMaintenanceWindow(ServiceWindow.Schedules, ServiceWindow.RecurrenceType) AS NextServiceWindow
        WHERE NextServiceWindow.NextServiceWindow IS NOT NULL
            AND ServiceWindowType <> 5 -- 排除OSD服务窗口
    ),
    MaintenanceInfo AS (
        SELECT ResourceID, NextServiceWindow
        FROM Maintenance_CTE
        WHERE RowNumber = 1
    ),
    UpdateInfo_CTE AS (
        SELECT
            Systems.ResourceID,
            Missing = COUNT(*)
        FROM fn_rbac_R_System(@UserSIDs) AS Systems
        JOIN fn_rbac_UpdateComplianceStatus(@UserSIDs) AS ComplianceStatus 
            ON ComplianceStatus.ResourceID = Systems.ResourceID
            AND ComplianceStatus.Status = 2 -- 仅筛选"需要安装"的更新
        JOIN fn_rbac_ClientCollectionMembers(@UserSIDs) AS CollectionMembers 
            ON CollectionMembers.ResourceID = ComplianceStatus.ResourceID
        JOIN fn_rbac_UpdateInfo(dbo.fn_LShortNameToLCID(@Locale), @UserSIDs) AS UpdateCIs 
            ON UpdateCIs.CI_ID = ComplianceStatus.CI_ID
            AND UpdateCIs.IsSuperseded IN (@Superseded)
            AND UpdateCIs.CIType_ID IN (1, 8) -- 筛选软件更新和更新捆绑包
            AND UpdateCIs.ArticleID NOT IN (
                SELECT VALUE FROM STRING_SPLIT(@ExcludeArticleIDs, ',')
            )
            AND UpdateCIs.Title NOT LIKE '[1-9][0-9][0-9][0-9]-[0-9][0-9]_Preview_of_%' -- 排除预览更新
        JOIN fn_rbac_CICategoryInfo_All(dbo.fn_LShortNameToLCID(@Locale), @UserSIDs) AS CICategory 
            ON CICategory.CI_ID = ComplianceStatus.CI_ID
            AND CICategory.CategoryTypeName = 'UpdateClassification'
            AND CICategory.CategoryInstanceName IN (@Categories) -- 筛选指定更新分类
        LEFT JOIN fn_rbac_CITargetedMachines(@UserSIDs) AS Targeted 
            ON Targeted.ResourceID = ComplianceStatus.ResourceID
            AND Targeted.CI_ID = ComplianceStatus.CI_ID
        WHERE CollectionMembers.CollectionID = @CollectionID
            AND IIF(Targeted.ResourceID IS NULL, 0, 1) IN (@Targeted) -- 筛选目标/非目标设备
            AND IIF(UpdateCIs.ArticleID = @ArticleID, 1, 0) = IIF(@ArticleID <> '', 1, 0)
        GROUP BY Systems.ResourceID
    ),
    HelperFunctionCheck AS (
        SELECT HelperFunctionExists = CASE WHEN OBJECT_ID('[dbo].[ufn_CM_GetNextMaintenanceWindow]') IS NOT NULL THEN 1 ELSE 0 END
    )
    SELECT
        Systems.ResourceID,
        HealthStates = (
            IIF(CombinedResources.IsClient != 1, POWER(1, 1), 0)
            + IIF(ClientSummary.ClientStateDescription IN ('Inactive/Pass', 'Inactive/Fail', 'Inactive/Unknown'), POWER(2, 1), 0)
            + IIF(ClientSummary.ClientStateDescription IN ('Active/Fail', 'Inactive/Fail'), POWER(4, 1), 0)
            + IIF(CombinedResources.ClientState != 0, POWER(8, 1), 0)
            + IIF(UpdateScan.LastErrorCode != 0, POWER(16, 1), 0)
            + IIF(UpdateScan.LastScanTime < DATEADD(dd, -14, CURRENT_TIMESTAMP), POWER(32, 1), 0)
            + IIF(ISNULL(Maintenance.NextServiceWindow, 0) = 0 AND (SELECT HelperFunctionExists FROM HelperFunctionCheck) = 1, POWER(64, 1), 0)
            + IIF(Maintenance.NextServiceWindow > DATEADD(dd, 30, CURRENT_TIMESTAMP), POWER(128, 1), 0)
            + IIF(Maintenance.NextServiceWindow < CURRENT_TIMESTAMP, POWER(256, 1), 0)
        ),
        Missing = ISNULL(UpdateInfo.Missing, IIF(CombinedResources.IsClient = 1, 0, NULL)),
        Device = (
            IIF(SystemNames.Resource_Names0 IS NOT NULL, UPPER(SystemNames.Resource_Names0),
                IIF(Systems.Full_Domain_Name0 IS NOT NULL, Systems.Name0 + '.' + Systems.Full_Domain_Name0, Systems.Name0)
            )
        ),
        OperatingSystem = (
            CASE
                WHEN OperatingSystem.Caption0 != '' THEN
                    CONCAT(
                        REPLACE(OperatingSystem.Caption0, 'Microsoft ', ''),
                        REPLACE(OperatingSystem.CSDVersion0, 'Service Pack ', ' SP')
                    )
                ELSE
                    CASE
                        WHEN CombinedResources.DeviceOS LIKE '%Workstation 6.1%' THEN 'Windows 7'
                        WHEN CombinedResources.DeviceOS LIKE '%Workstation 6.2%' THEN 'Windows 8'
                        WHEN CombinedResources.DeviceOS LIKE '%Workstation 6.3%' THEN 'Windows 8.1'
                        WHEN CombinedResources.DeviceOS LIKE '%Workstation 10.0%' THEN 'Windows 10'
                        WHEN CombinedResources.DeviceOS LIKE '%Server 6.0' THEN 'Windows Server 2008'
                        WHEN CombinedResources.DeviceOS LIKE '%Server 6.1' THEN 'Windows Server 2008R2'
                        WHEN CombinedResources.DeviceOS LIKE '%Server 6.2' THEN 'Windows Server 2012'
                        WHEN CombinedResources.DeviceOS LIKE '%Server 6.3' THEN 'Windows Server 2012 R2'
                        WHEN Systems.Operating_System_Name_And0 LIKE '%Server 10%' THEN
                            CASE WHEN CAST(REPLACE(Build01, '.', '') AS INTEGER) > 10017763 THEN 'Windows Server 2019' ELSE 'Windows Server 2016' END
                        ELSE Systems.Operating_System_Name_And0
                    END
            END
        ),
        LastBootTime = CONVERT(NVARCHAR(16), OperatingSystem.LastBootUpTime0, 120),
        PendingRestart = (
            CASE
                WHEN CombinedResources.IsClient = 0 OR CombinedResources.ClientState = 0 THEN NULL
                ELSE
                    STUFF(
                        REPLACE(
                            (SELECT '#!' + LTRIM(RTRIM(StateName)) AS [data()]
                             FROM ClientState
                             WHERE BitMask & CombinedResources.ClientState <> 0
                             FOR XML PATH('')),
                            ' #!', ', '
                        ),
                        1, 2, ''
                    )
            END
        ),
        ClientState = CASE CombinedResources.IsClient WHEN 1 THEN ClientSummary.ClientStateDescription ELSE 'Unmanaged' END,
        ClientVersion = CombinedResources.ClientVersion,
        LastUpdateScan = CONVERT(NVARCHAR(16), UpdateScan.LastScanTime, 120),
        LastScanLocation = NULLIF(UpdateScan.LastScanPackageLocation, ''),
        LastScanError = NULLIF(UpdateScan.LastErrorCode, 0),
        NextServiceWindow = IIF(CombinedResources.IsClient != 1, NULL, CONVERT(NVARCHAR(16), Maintenance.NextServiceWindow, 120))
    FROM fn_rbac_R_System(@UserSIDs) AS Systems
    JOIN fn_rbac_CombinedDeviceResources(@UserSIDs) AS CombinedResources 
        ON CombinedResources.MachineID = Systems.ResourceID
    LEFT JOIN fn_rbac_RA_System_ResourceNames(@UserSIDs) AS SystemNames 
        ON SystemNames.ResourceID = Systems.ResourceID
    LEFT JOIN fn_rbac_GS_OPERATING_SYSTEM(@UserSIDs) AS OperatingSystem 
        ON OperatingSystem.ResourceID = Systems.ResourceID
    LEFT JOIN fn_rbac_CH_ClientSummary(@UserSIDs) AS ClientSummary 
        ON ClientSummary.ResourceID = Systems.ResourceID
    LEFT JOIN fn_rbac_UpdateScanStatus(@UserSIDs) AS UpdateScan 
        ON UpdateScan.ResourceID = Systems.ResourceID
    LEFT JOIN MaintenanceInfo AS Maintenance 
        ON Maintenance.ResourceID = Systems.ResourceID
    LEFT JOIN UpdateInfo_CTE AS UpdateInfo 
        ON UpdateInfo.ResourceID = Systems.ResourceID
    JOIN fn_rbac_FullCollectionMembership(@UserSIDs) AS CollectionMembers 
        ON CollectionMembers.ResourceID = Systems.ResourceID
    CROSS JOIN HelperFunctionCheck
    WHERE CollectionMembers.CollectionID = @CollectionID
        AND (
            CASE -- 合规状态：0=不合规，1=合规，2=未知
                WHEN UpdateInfo.Missing = 0 OR (UpdateInfo.Missing IS NULL AND Systems.Client0 = 1) THEN 1
                WHEN UpdateInfo.Missing > 0 AND UpdateInfo.Missing IS NOT NULL THEN 0
                ELSE 2
            END
        ) IN (@Compliant)
)
GO

使用方式 #

-- 传入参数查询，效果与原查询完全一致
SELECT * FROM dbo.DeviceUpdateStatus(
    'Disabled',
    'SMS00001',
    2,
    'Tools',
    0,
    1,
    0,
    '',
    ''
)

方案2：创建存储过程生成物理表 #

如果需要将固定参数的结果持久化为物理表，可通过存储过程定期刷新数据。

存储过程代码 #

CREATE PROCEDURE dbo.RefreshDeviceUpdateStatusTable
    @UserSIDs NVARCHAR(10) = 'Disabled',
    @CollectionID NVARCHAR(10) = 'SMS00001',
    @Locale INT = 2,
    @Categories NVARCHAR(250) = 'Tools',
    @Compliant INT = 0,
    @Targeted INT = 1,
    @Superseded INT = 0,
    @ArticleID NVARCHAR(10) = '',
    @ExcludeArticleIDs NVARCHAR(250) = ''
AS
BEGIN
    SET NOCOUNT ON;

    -- 创建物理表（如果不存在）
    IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'DeviceUpdateStatus')
    BEGIN
        CREATE TABLE DeviceUpdateStatus (
            ResourceID INT,
            HealthStates INT,
            Missing INT,
            Device NVARCHAR(256),
            OperatingSystem NVARCHAR(256),
            LastBootTime NVARCHAR(16),
            PendingRestart NVARCHAR(256),
            ClientState NVARCHAR(100),
            ClientVersion NVARCHAR(50),
            LastUpdateScan NVARCHAR(16),
            LastScanLocation NVARCHAR(256),
            LastScanError INT,
            NextServiceWindow NVARCHAR(16)
        )
    END

    -- 清空现有数据
    TRUNCATE TABLE DeviceUpdateStatus;

    -- 插入新数据（保留原查询的变量和临时表逻辑）
    DECLARE @LCID AS INT = dbo.fn_LShortNameToLCID(@Locale);
    DECLARE @HelperFunctionExists AS INT = 0;

    IF OBJECT_ID('tempdb..#MaintenanceInfo', 'U') IS NOT NULL
        DROP TABLE #MaintenanceInfo;

    IF OBJECT_ID('[dbo].[ufn_CM_GetNextMaintenanceWindow]') IS NOT NULL
        SET @HelperFunctionExists = 1;

    DECLARE @HealthState TABLE (
        BitMask INT,
        StateName NVARCHAR(250)
    )

    INSERT INTO @HealthState (BitMask, StateName)
    VALUES
        (0,     'Healthy'),
        (1,   'Unmanaged'),
        (2,   'Inactive'),
        (4,   'Health Evaluation Failed'),
        (8,   'Pending Restart'),
        (16,  'Update Scan Failed'),
        (32,  'Update Scan Late'),
        (64,  'No Maintenance Window'),
        (128, 'Distant Maintenance Window'),
        (256, 'Expired Maintenance Window')

    DECLARE @ClientState TABLE (
        BitMask INT,
        StateName NVARCHAR(100)
    )

    INSERT INTO @ClientState (BitMask, StateName)
    VALUES
        (0, 'No Reboot'),
        (1, 'Configuration Manager'),
        (2, 'File Rename'),
        (4, 'Windows Update'),
        (8, 'Add or Remove Feature')

    CREATE TABLE #MaintenanceInfo (
        ResourceID INT,
        NextServiceWindow DATETIME
    )

    IF @HelperFunctionExists = 1
    BEGIN
        WITH Maintenance_CTE AS (
            SELECT
                CollectionMembers.ResourceID,
                NextServiceWindow.NextServiceWindow,
                RowNumber = DENSE_RANK() OVER (PARTITION BY ResourceID ORDER BY NextServiceWindow.NextServiceWindow)
            FROM vSMS_ServiceWindow AS ServiceWindow
            JOIN fn_rbac_FullCollectionMembership(@UserSIDs) AS CollectionMembers ON CollectionMembers.CollectionID = ServiceWindow.SiteID
            JOIN fn_rbac_Collection(@UserSIDs) AS Collections ON Collections.CollectionID = CollectionMembers.CollectionID
                AND Collections.CollectionType = 2
            CROSS APPLY ufn_CM_GetNextMaintenanceWindow(ServiceWindow.Schedules, ServiceWindow.RecurrenceType) AS NextServiceWindow
            WHERE NextServiceWindow.NextServiceWindow IS NOT NULL
                AND ServiceWindowType <> 5
        )
        INSERT INTO #MaintenanceInfo(ResourceID, NextServiceWindow)
        SELECT ResourceID, NextServiceWindow FROM Maintenance_CTE WHERE RowNumber = 1
    END

    ;WITH UpdateInfo_CTE
    AS (
        SELECT
            ResourceID = Systems.ResourceID,
            Missing = COUNT(*)
        FROM fn_rbac_R_System(@UserSIDs) AS Systems
        JOIN fn_rbac_UpdateComplianceStatus(@UserSIDs) AS ComplianceStatus ON ComplianceStatus.ResourceID = Systems.ResourceID
            AND ComplianceStatus.Status = 2
        JOIN fn_rbac_ClientCollectionMembers(@UserSIDs) AS CollectionMembers ON CollectionMembers.ResourceID = ComplianceStatus.ResourceID
        JOIN fn_rbac_UpdateInfo(@LCID, @UserSIDs) AS UpdateCIs ON UpdateCIs.CI_ID = ComplianceStatus.CI_ID
            AND UpdateCIs.IsSuperseded IN (@Superseded)
            AND UpdateCIs.CIType_ID IN (1, 8)
            AND UpdateCIs.ArticleID NOT IN (SELECT VALUE FROM STRING_SPLIT(@ExcludeArticleIDs, ','))
            AND UpdateCIs.Title NOT LIKE '[1-9][0-9][0-9][0-9]-[0-9][0-9]_Preview_of_%'
        JOIN fn_rbac_CICategoryInfo_All(@LCID, @UserSIDs) AS CICategory ON CICategory.CI_ID = ComplianceStatus.CI_ID
            AND CICategory.CategoryTypeName = 'UpdateClassification'
            AND CICategory.CategoryInstanceName IN (@Categories)