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

Question

我试图通过将查询结果传递给我的参数来获取存储过程的结果。

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' 

当试图使用资产计数作为有价值的参数执行存储过程时出现语法错误。任何帮助，将不胜感激。

谢谢。

Answer 1

首先，您声明两次@AssetStatus参数。

其次，您将count列别名为'AssetCount'，但不要在其他任何地方使用它，所以我不确定为什么您认为这会导致语法错误。

最后，你问：

“我试图通过将查询的结果我的参数来获取存储过程的结果。”

这是什么意思？你是否试图从本身递归调用存储过程？或者你是否试图将另一个查询的结果传递给此存储过程？更多细节将有所帮助！如果您对评论做出回应，我会尽力为您提供帮助 - 无论您的问题是什么，我都不认为这会很难解决。