Динамический запрос не выполняется в EXEC

#sql #tsql #dynamic

#sql #tsql #динамический

Вопрос:

В приведенном ниже запросе, когда я использую оператор print, полный запрос выводится, как и ожидалось, и я могу его подобрать и выполнить без проблем. Но если вместо его печати я запускаю его с помощью EXEC, я получаю сообщение об ошибке, в котором говорится о неправильном синтаксисе, принимая некоторую часть запроса и сообщая, что это неверный идентификатор, как будто исполнитель просто видит частичный запрос, а не полный. Как вы можете видеть, я использую varchar(max), который должен соответствовать всей строке запроса. У кого-нибудь есть какие-либо идеи здесь? Спасибо!

 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].amp;["  Format(Now(),"yyyyMM")   "]").lag(12)
:StrToMember("[Trans Date].[Year - Quarter - Month - Date].[Month].amp;["  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].amp;['''   @RollUp   ''']
) on 1  FROM SALES
)'''


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

print @sql1

EXEC @SQL1

Ответ №1:

Таблица #Result не известна при запуске exec . Он определен во внешней области, но не наследуется во внутренней области. Вы не можете использовать временные таблицы, подобные этой, если только они не являются глобальными временными таблицами (которым предшествует ## вместо просто # ).

Кроме того, вы никогда не должны использовать varchar() в SQL Server без длины. Длина по умолчанию зависит от контекста и может быть недостаточно длинной. Другими словами, у вас должна быть длина для varchar() в convert() инструкциях.

1. Спасибо за ваш ответ, я обновлю varchar, это был ленивый шаг с моей стороны, однако я не думаю, что это проблема. Это внутри SP, объявление переменной на уровне SP делает ее доступной для Exec динамического запроса без необходимости в глобальной переменной. Глобальные переменные опасны, потому что к ним имеют доступ несколько сеансов, и их можно изменить. В моем случае мне нужно, чтобы все содержалось в текущем запущенном сеансе sp.

Ответ №2:

Я исправил, разбив запрос на 4 подстроки, затем в конце просто сделайте

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

Я не уверен, как varchar(max) не принимает динамическую строку, длина которой явно не превышает 1000 символов. Если только редактор не добавляет скрытые символы.

В любом случае исправлено.