How to handle an empty result set from an OpenQuery call to linked analysis server in dynamic SQL?

I have a number of stored procedures structured similarly to this:

DECLARE @sql NVARCHAR(MAX)
DECLARE @mdx NVARCHAR(MAX)

CREATE table #result
(
  [col1] NVARCHAR(50),
  [col2] INT,
  [col3] INT
)

SET @mdx = '{some dynamic MDX}'
SET @sql = 'SELECT a.* FROM OpenQuery(LinkedAnalysisServer, ''' + @mdx + ''') AS a'

INSERT INTO #result
EXEC sp_executesql @sql
SELECT * FROM #result

This works quite well when results exist in the cube. However, when the OpenQuery results are empty, the INSERT fails with this error:

Column name or number of supplied values does not match table definition.

My question is, what is the best way to handle this scenario? I'm using the results in a static report file (.rdlc), so the explicit typin开发者_如何学编程g of the temp table is (I'm pretty sure) required.

Use TRY/CATCH in your stored procedure, you'll notice there is a specific error number for your problem, so check the error number and if it is that, return an empty result set. As you already have the table defined that'll be easier.

PseudoCode looks something like this:

SET @mdx = '{some dynamic MDX}'
SET @sql = 'SELECT a.* FROM OpenQuery(LinkedAnalysisServer, ''' + @mdx + ''') AS a'

BEGIN TRY

INSERT INTO #result
EXEC sp_executesql @sql

END TRY
BEGIN CATCH

  IF ERROR_NUMBER <> 'The error number you are seeing'
  BEGIN
    RAISERROR('Something happened that was not an empty result set')
  END

END CATCH

SELECT * FROM #result

You'll want to check for that particular error, so that you don't just return empty result sets if your SSAS server crashes for example.

There is another solution to this issue, similar to the accepted answer, which involves using an IF statement instead of TRY...CATCH.

http://www.triballabs.net/2011/11/overcoming-openquery-mdx-challenges/

IF (SELECT COUNT(*)
    FROM OPENQUERY("SSAS1",
      'SELECT [Measures].[Target Places] ON COLUMNS
      FROM [ebs4BI_FactEnrolment]
      WHERE [DimFundingYear].[Funding Year].&[17]')) > 0
  EXEC sp_executesql N'SELECT CONVERT(varchar(20),
  "[DimPAPSCourse].[Prog Area].[Prog Area].[MEMBER_CAPTION]")
  as ProgArea,
  convert(float, "[Measures].[Target Places]") as Target
  FROM OPENQUERY("SSAS1",
    ''SELECT [Measures].[Target Places] ON COLUMNS,
    [DimPAPSCourse].[Prog Area].[Prog Area] ON ROWS
    FROM [ebs4BI_FactEnrolment]
    WHERE [DimFundingYear].[Funding Year].&[17]'')'
ELSE
  SELECT '' as ProgArea, 0 as Target
  WHERE 1=0