问将查询结果传递给存储过程中的参数

EN

ALTER PROCEDURE [DWH].[spAMBSiteAssetCountReport] 
    @Areaname, @SiteType, @EquipmentClass, @AssetStatus
    @MaintenanceLocation varchar(Max),
    @FLClassDescription varchar(max),
    @EquipmentClass varchar(max),
    @AssetStatus varchar(max)
As
Begin
    SET NOCOUNT ON;

    SET ANSI_WARNINGS OFF -- to suppress warning "string or binary data would be truncated"

    SELECT  
        EC.ClassDescription, FL.Site, 
        SUBSTRING(FL.Site, CHARINDEX('-',FL.Site)+1,LEN(FL.Site)) SiteNo, 
        FL.SiteDesc, FL.FunctionalLocation, FL.Parish, 
        ST.SiteTypeName FLClassDescription, FL.MaintenanceLocationDesc,
        Count(1) AS AssetCount
    FROM    
        DWH.DimFunctionalLocation AS FL 
    INNER JOIN 
        DWH.DimEquipment AS EQ ON EQ.FunctionalLocationKey = FL.DW_FunctionalLocation_Key  
    INNER JOIN 
        DWH.DimEquipmentDetail AS EQD ON EQ.DW_Equipment_Key = EQD.EquipmentKey
    INNER JOIN 
        DWH.DimEquipmentClass AS EC ON EC.DW_EquipmentClass_Key = EQD.DW_EquipmentDetail_Key
    INNER JOIN 
        DWH.DimSiteType ST ON FL.SiteTypeKey = ST.DW_SiteType_Key
    INNER JOIN 
        (select Item 
         from DWH.fnSplit(@FLClassDescription,',')) AS DNO ON (FL.SiteTypeKey = DNO.Item OR @FLClassDescription ='-1')
    INNER JOIN 
        (select Item from DWH.fnSplit(@MaintenanceLocation,',')) AS ML ON (FL.MaintenanceLocationKey = ML.Item OR @MaintenanceLocation ='-1')
    INNER JOIN 
        (select Item from DWH.fnSplit(@EquipmentClass,',')) AS FLC ON (EC.DW_EquipmentClass_Key = FLC.Item OR @EquipmentClass ='-1')
    INNER JOIN 
        (select Item from DWH.fnSplit(@AssetStatus,',')) AS EQC ON ((EQD.CharacteristicName = EQC.Item AND ISNULL(LTRIM(RTRIM(EQD.CHARCharacteristicValue)),'') <> '')  OR  @AssetStatus ='All')
    WHERE 
        FL.FLClassDescription IS NOT NULL
    GROUP BY 
        EC.ClassDescription, FL.Site, FL.SiteDesc, 
        FL.FunctionalLocation, FL.Parish, ST.SiteTypeName, 
        FL.MaintenanceLocationDesc
END

Exec DWH.spAMBSiteAssetCountReport @MaintenanceLocation=N'366,367,332,362,3,360,331,365,361,364,357,396,2,406,371,4,368,369,370,333,394,358,359,395,355,353,354,335,363,356,397,352,349,348,351,350,347,372,373,374,377,375,376,382,386,383,387,384,389,381,391,378,385,379,380,388,390',@FLClassDescription=N'3,4,5,2,1',@EquipmentClass = AssetCount,@AssetStatus=N'All'