在存储过程中执行 FuncView

Question

我正在尝试修改 SQL 2000 中现有的 SPROC。原始 SPROC 使用 FuncView 作为游标的一部分。 新报告不需要游标，但我不确定如何在游标语句之外执行 FuncView。 下面的第一个例子是原始代码的光标部分。 第二个是我的修改......以及由此产生的错误消息。 请帮忙...

DECLARE @FuncView_PlantConditionAssessmentMetrics VARCHAR(8000)
DECLARE FuncView_PlantConditionAssessmentMetricsCursor CURSOR FOR 
    Select 
        'UPDATE #rsTemp
            SET  TotalComponentsOnTask = rsMetric.TotalComponentsOnTask,
            TotalComponentsAssessedMeasured = rsMetric.TotalComponentsAssessedMeasured,
            TotalComponentsNotMeasured = rsMetric.TotalComponentsNotMeasured,
            TotalComponentsAssessedNotMeasured = rsMetric.TotalComponentsAssessedNotMeasured,
            OverdueElevatedItems = rsMetric.OverdueElevatedItems,
            OverdueAdvisoryItems = rsMetric.OverdueAdvisoryItems
        FROM ['+DBCatalog+'].[dbo].FuncView_PlantConditionAssessmentMetrics ('''+CONVERT(varchar(50), @StartDate, 100)+''','''+ CONVERT(varchar(50), @EndDate, 100)+''') rsMetric
        WHERE DBCatalog = '''+DBCatalog+''''
    FROM [HostedTangoSettingsSQL].[dbo].[DatabaseTable]
    WHERE CustomerID = @CustomerID
        AND
        HostedDBTypeID IN (SELECT HostedDBTypeID FROM [HostedTangoSettingsSQL].[dbo].defs_HostedDBTypes
                 WHERE IsActiveDatabase = 1 AND HostedDBTypeID <> 3)


OPEN FuncView_PlantConditionAssessmentMetricsCursor
FETCH NEXT FROM FuncView_PlantConditionAssessmentMetricsCursor INTO @FuncView_PlantConditionAssessmentMetrics
WHILE (@@fetch_status <> -1)
    BEGIN
        EXEC(@FuncView_PlantConditionAssessmentMetrics)
        FETCH NEXT FROM FuncView_PlantConditionAssessmentMetricsCursor INTO @FuncView_PlantConditionAssessmentMetrics
    END
CLOSE FuncView_PlantConditionAssessmentMetricsCursor
DEALLOCATE FuncView_PlantConditionAssessmentMetricsCursor



DECLARE @FuncView_PlantConditionAssessmentMetrics VARCHAR(8000)

SELECT FuncView_PlantConditionAssessmentMetrics = @FuncView_PlantConditionAssessmentMetrics
    'UPDATE #rsTemp
        SET  TotalComponentsOnTask = rsMetric.TotalComponentsOnTask,
            TotalComponentsAssessedMeasured = rsMetric.TotalComponentsAssessedMeasured,
            TotalComponentsNotMeasured = rsMetric.TotalComponentsNotMeasured,
            TotalComponentsAssessedNotMeasured = rsMetric.TotalComponentsAssessedNotMeasured,
            OverdueElevatedItems = rsMetric.OverdueElevatedItems,
            OverdueAdvisoryItems = rsMetric.OverdueAdvisoryItems
        FROM ['+@CustomerDesc+'].[dbo].FuncView_PlantConditionAssessmentMetrics ('''+CONVERT(varchar(50), @StartDate, 100)+''','''+ CONVERT(varchar(50), @EndDate, 100)+''') rsMetric
        WHERE DBCatalog = '''+@CustomerDesc+''''


Server: Msg 170, Level 15, State 1, Procedure UDR_PlantConditionAssessmentMetrics, Line 54
Line 54: Incorrect syntax near 'UPDATE #rsTemp
        SET  TotalComponentsOnTask = rsMetric.TotalComponentsOnTask,
            TotalComponentsAssessedMeasured = rsMetric.To'.

Answer 1

哇，游标和动态 SQL； 你正在接触各种坏习惯，不是吗:)这是学习一些东西的好方法，但在维护方面却很糟糕。

看起来你的代码需要这样调整：

SELECT  @FuncView_PlantConditionAssessmentMetrics = 
    'UPDATE #rsTemp
        SET  TotalComponentsOnTask = rsMetric.TotalComponentsOnTask,
                TotalComponentsAssessedMeasured = rsMetric.TotalComponentsAssessedMeasured,
                TotalComponentsNotMeasured = rsMetric.TotalComponentsNotMeasured,
                TotalComponentsAssessedNotMeasured = rsMetric.TotalComponentsAssessedNotMeasured,
                OverdueElevatedItems = rsMetric.OverdueElevatedItems,
                OverdueAdvisoryItems = rsMetric.OverdueAdvisoryItems 
        FROM ['
        + @CustomerDesc + '].[dbo].FuncView_PlantConditionAssessmentMetrics (''' + CONVERT(VARCHAR(50), @StartDate, 100) + ''','''
        + CONVERT(VARCHAR(50), @EndDate, 100) + ''') rsMetric        WHERE DBCatalog = ''' + @CustomerDesc + ''''

为了使其与之前的代码一致。