Executing a FuncView within a Stored Procedure

Question

I am trying to modify an exisiting SPROC in SQL 2000. The original SPROC used a FuncView as part of a a cursor. The new report does not require the cursor, but I am unsure how to execute the FuncView outside of the cursor statement. The first example below is the cursor part of the original code. The second is my modification....with the resulting error message. Please help...

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

WOW, cursors AND dynamic SQL; you're getting exposed to all kinds of bad habits, aren't you :) It's a good way to learn something, but it sucks when it comes to maintenance.

It looks like your code needs to be adjusted thusly:

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 + ''''

In order to make it consistent with the previous code.