CODEWITHSUNDEEP
sqlt-sqldynamic
user167908
user167908

Reputation: 977

Dynamic query does not run on EXEC

In the query below, when I use the print statement the full query prints out as expected and I can pick it up and execute no problems. But if instead of printing it, I run it with EXEC, I get an error which says incorrect syntax by taking some portion of the query and saying that it's an incorrect identifier, as if the executor just sees a partial query and not the full thing. As you can see, I am using varchar(max), which ought to fit the entire query string. Anyone have any ideas here? Thanks!

declare @RollUp varchar = "hello"
DECLARE @SQL VARCHAR(MAX) 
SET @SQL =
'INSERT INTO #RESULT 
SELECT CONVERT(VARCHAR,"[Member0].[MEMBER_CAPTION]") AS Zeroth,
CONVERT(VARCHAR,"[Member1].[MEMBER_CAPTION]") AS First,
CONVERT(VARCHAR,"[Member2].[MEMBER_CAPTION]") AS Second,
CONVERT(VARCHAR,"[Member3].[MEMBER_CAPTION]") AS Third,
CONVERT(VARCHAR,"[Member4].[MEMBER_CAPTION]") AS Fourth,
CONVERT(VARCHAR,"[Member5].[MEMBER_CAPTION]") AS Fifth,
CONVERT(VARCHAR,"[Member6].[MEMBER_CAPTION]") AS Sixth,
CONVERT(VARCHAR,"[Member7].[MEMBER_CAPTION]") AS Seventh,
CONVERT(MONEY,"[Measures].[MyMeasure]") AS Eighth
FROM OPENROWSET(''MSOLAP'',''DataSource=MyServer;Initial Catalog=Sales'' ,''    
WITH MEMBER [Measures].[MyMeasure]
AS (SUM (StrToMember("[Trans Date].[Year - Quarter - Month - Date].[Month].&["+ Format(Now(),"yyyyMM") + "]").lag(12)
:StrToMember("[Trans Date].[Year - Quarter - Month - Date].[Month].&["+ Format(Now(),"yyyyMM") + "]").lag(1)    
,[Measures].[Revenue])) 
SELECT NON EMPTY([Measures].[MyMeasure]) on 0,
NON EMPTY({[Commission Category Current].[EP Business Line].[Business Line].members *
[Sales].[Product].members *
[Territory].[Territories].[Territory].members *
[Purchasing Site].[Customers].[Customer].members *
[Purchasing Site].[Cust ID].Children *
[Site].[Customers].[Customer].members *
[Site].[Cust ID].Children} *
[Territory].[Countries].[Territory RollUp].&[''' + @RollUp + ''']
) on 1  FROM SALES
)'''


DECLARE @SQL1 VARCHAR(MAX)= Replace(Replace(@SQL, '[''', '['), ''']', ']')

print @sql1

EXEC @SQL1

Upvotes: 0

Views: 92

Answers (2)

user167908
user167908

Reputation: 977

I fixed by breaking up the query in 4 substrings, then at the end just do

EXEC (@STR1 + @STR2 + @STR3 + @STR4).

I'm not sure how a varchar(max) doesn't accept a dynamic string that is clearly not greater than 1000 characters long. Unless the editor is adding hidden chars.

In any event, fixed.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

The table #Result is not known when you run exec. It is defined in the outer scope but not inherited in the inner scope. You cannot use temporary tables like this, unless they are global temporary tables (preceded by ## instead of just #).

Also, you should never use varchar() in SQL Server without a length. The default length depends on the context and might not be long enough. In other words, you should have a length for varchar() in the convert() statements.

Upvotes: 1

Related Questions