SQL 2005: OpenRowset, Dynamic MDX and Variable Scope

So, the other day I had to create something in T-SQL that called a MDX query using OpenRowset – this is pretty easy to do, you can query around the openrowset and get the values back you need in a T-SQL Query. This was fine when the MDX query was a static string.

The format of the query would be like this:

SELECT * FROM OpenRowset(‘MSOLAP’, ‘DATA SOURCE=MySSASServer; Initial Catalog=MySSASDB;’,’MyMDXQuery HERE’) as a

Now, the * will give you the columns from your MDX query, in the example above MyMDXQuery would be replaced with your actual MDX query.

The problem comes in, if you want your MDX query (which is a string), to contain some variable, so that you can pass something into the OpenRowset (say a date, or some other variable)…

The problem is, you need to execute the whole query (not just the OpenRowset) as a string, and the scope of variables is lost. You cannot declare a variable outside the TSQL string you want to EXEC, then set it inside the TSQL statement, then use it after. This makes it tough to get data out of the OpenRowset execution. Now if you just are executing the TSQL and getting a result set back for a report or something, it will work without doing what I am doing here, but if you need a scalar value back or something to use in a query later in your proc, then you need to do this. I tried different solutions and this was the only one I could get to work. Like I said, declaring a var before and trying to use in the TSQL exec wont work. Also, a RETURN wont work, it will give you an error saying it doesn’t work in the scope or something similar, here is an example of what does work – using a temp table.

DECLARE @TSQL varchar(max)

CREATE TABLE #results
(
  mytempresult DECIMAL(10, 3)
)

SET @TSQL = ‘
DECLARE @myVar AS DECIMAL(10,3)

SELECT @myVar =
SELECT [Measures].[MyMeasure] FROM
OpenRowset(
”MSOLAP”,
”DATA SOURCE=MySSASServer; Initial Catalog=MySSASDB;”,
”WITH
   MEMBER Measures.[MyMeasure]
  AS (‘ + @SomeDynamicString + ‘)
SELECT
{[Measures].[MyMeasure]} ON COLUMNS
FROM [MyCube]
”)
as a

INSERT INTO #results VALUES (@myVar)
‘

EXEC ( @TSQL )

DECLARE @myVarForReal AS DECIMAL(10, 3)
SELECT  @myVarForReal = mytempresult
FROM    #results

DROP TABLE #results

as you can see, I CREATE the temp table outside the TSQL var, then I actually declare a var inside the TSQL statement, set it in my OpenRowset call, which I pass in some other var (@SomeDynamicString) and then insert that value into my temp table.

I then EXEC that TSQL statement, and then grab my variable for real from the temp table, and drop the temp table. You would think that I could just reference @myVar after the EXEC, but it doesn’t exist, and if I declare it outside the TSQL var, it will be empty after, and it won’t get set when I EXEC the TSQL.

Just a “gotcha” if you ever run into executing dynamic MDX from TSQL and you need to get a scalar value back from the MDX.. whew 🙂