下面的动态SQL失败了，但当我手动运行它时，它可以正常工作

Question

我使用sp_executesql运行这个内置在字符串中的SQL语句，但它总是失败，并出现以下错误：

Msg 103010，级别16，状态1，第1行
第4行第1列的分析错误："GO"附近的语法错误。

这是代码：

DECLARE @sql NVARCHAR(MAX) = '';
SET @sql = N'BEGIN TRY
IF object_id(N''[DW.myview]'', ''V'') IS NOT NULL
DROP VIEW [DW.myview]
GO
CREATE VIEW [DW.myview] 
AS
SELECT *
FROM
(SELECT
field_name, field_value, ww, id, tenant,
revision, rls_group_id, is_current,
updated_date, system_updated_date
FROM
dbo.myview
WHERE
field_name IS NOT NULL
AND field_name <> '''') t 
PIVOT(MAX(field_value) 
FOR field_name IN ([topic.ar_gen]
, [topic.build]
, [topic.build_found]
, [topic.ccb_change_scope]
, [topic.ccb_order]
, [topic.ccb_por]
, [topic.ccb_prq_gating]
, [topic.ccb_questionnaire]
, [topic.ccb_status]
, [topic.ccb_wontfix_action]
, [topic.ccb_wontfix_reason]
, [topic.ccb_workaround]
, [layer.defined_date]
, [layer.drop_fix_plan]
, [layer.drop_found]
, [layer.env_found]
, [layer.errata_info]
, [layer.errata_info_owner]
, [layer.errata_status]
, [layer.failure_signature]
, [layer.forum]
, [layer.merge_id]
, [layer.open_date]
, [layer.por]
, [layer.release_found]
, [layer.repo_modified_date]
, [layer.root_cause]
, [layer.status_mode]
, [layer.status_reopen]
, [layer.team_found]
, [layer.test_found]
, [layer.to_reproduce]
, [layer.type]
, [layer.ww_defined_date]
, [layer.ww_open_date]
, [layer.ww_repo_modified_date]
, [classification]
, [closed_by]
, [closed_date]
, [comments]
, [component]
, [component_affected]
, [concat_read_grps_id]
, [concat_write_grps_id]
, [context]
, [description]
, [domain]
, [domain_affected]
, [eid]
, [family]
, [from_id]
, [from_subject]
, [from_tenant]
, [hierarchy_path]
, [subtopic.layer.affected_domain]
, [subtopic.layer.affecting_BAT]
, [subtopic.layer.affecting_certification]
, [subtopic.layer.affecting_compliance]
, [subtopic.layer.affecting_DPMO]
, [subtopic.layer.affecting_regression]
, [subtopic.layer.affecting_WiAMT]
, [subtopic.layer.applicable_derivatives]
, [subtopic.layer.applicable_projects_in_OTM]
, [subtopic.layer.AR_owner]
, [subtopic.layer.ATransition]
, [subtopic.layer.boot_device]
, [subtopic.layer.branch]
, [subtopic.layer.bug_communication]
, [subtopic.layer.bug_escape_category]
, [subtopic.layer.bug_escape_resolution]
, [subtopic.layer.bug_escape_stage]
, [subtopic.layer.bug_escape_status]
, [subtopic.layer.CCE_status]
, [subtopic.layer.changelist]
, [subtopic.layer.customer]
, [subtopic.layer.customer_impact]
, [subtopic.layer.date_moved_to_assigned]
, [higher_level.layer..layer.dcg_exposure]
, [higher_level.layer.dcg_priority]
, [higher_level.layer.duplicate_date]
, [higher_level.layer.exposure]
, [higher_level.layer.families_affected]
, [higher_level.layer.fix_stage]
, [higher_level.layer.fixed_by]
, [higher_level.layer.fixed_in_project_build]
, [higher_level.layer.found_in_derivatives]
, [higher_level.layer.future_cloned_bug_id]
, [higher_level.layer.FW_release]
, [higher_level.layer.hh_current_user]
, [higher_level.layer.HSD_Classic_DB]
, [higher_level.layer.HSD_Classic_Focus]
, [higher_level.layer.HSD_closed_by]
, [higher_level.layer.HSD_submitted_by]
, [higher_level.layer.intel_plarform]
, [higher_level.layer.intel_Platfrom_affected]
, [higher_level.layer.internal_record_update]
, [higher_level.layer.IP]
, [higher_level.layer.is_DevOps_user]
, [higher_level.layer.is_FIT_user]
, [higher_level.layer.is_OK_to_forward]
, [higher_level.layer.is_security_group_user]
, [higher_level.layer.NP_sysdebug_status]
, [higher_level.layer.operating_system]
, [higher_level.layer.pch_list]
, [higher_level.layer.platform]
, [higher_level.layer.platform_type]
, [higher_level.layer.PO_sysdebug_status]
, [higher_level.layer.prev_component]
, [higher_level.layer.prev_component_affected]
, [higher_level.layer.product]
, [higher_level.layer.project]
, [higher_level.layer.rejected_approved]
, [higher_level.layer.rejected_approved_notes]
, [higher_level.layer.rejected_date]
, [higher_level.layer.related_issue_id]
, [higher_level.layer.release_trigger]
, [higher_level.layer.release_type]
, [higher_level.layer.reproducibility]
, [higher_level.layer.reviewed_by]
, [higher_level.layer.reviewed_by_CCE]
, [higher_level.layer.root_cause_analysis]
, [higher_level.layer.security_legal_impact]
, [higher_level.layer.security_level]
, [higher_level.layer.sku_affected]
, [higher_level.layer.sku_list]
, [higher_level.layer.submitted_for_tracking_only]
, [higher_level.layer.submitter_org]
, [higher_level.layer.sysdebug_notes]
, [higher_level.layer.sysdebug_review_status]
, [higher_level.layer.target_MS]
, [higher_level.layer.target_QSR_release]
, [higher_level.layer.target_release]
, [higher_level.layer.validator]
, [higher_level.layer.version_defect_verified]
, [higher_level.layer.ww_date_moved_to_assigned]
, [higher_level.layer.ww_duplicate_date]
, [higher_level.layer.ww_rejected_date]
, [kinship]
, [lineage]
, [link_direction]
, [link_type]
, [native]
, [nickname]
, [notify]
, [owner]
, [parent_id]
, [permission_id_list]
, [permission_override]
, [priority]
, [read_grps]
, [reason]
, [reason_other]
, [relationship]
, [relationship_action]
, [release]
, [release_affected]
, [sampletime]
, [send_mail]
, [sets]
, [source_read_grps]
, [source_read_grps_id]
, [source_write_grps]
, [source_write_grps_id]
, [status]
, [status_reason]
, [subject]
, [submitted_by]
, [submitted_date]
, [subsystem]
, [sync_action]
, [tag]
, [tenant_affected]
, [title]
, [updated_by]
, [updated_reason]
, [write_grps]
, [write_grps_id]
, [week_closed_date]
, [week_submitted_date]
, [week_system_updated_date]
, [week_updated_date]) ) AS pivot_table
;
END TRY
BEGIN CATCH
EXEC usp_GetErrorInfo
END CATCH'
EXECUTE sp_executesql @sql;

有趣的是，如果我从字符串中提取SQL并像往常一样运行它(F5(，它会很好地创建带有预期SQL的预期视图。

我正在使用Azure SQL数据仓库(现在称为Synapse(

上面的SQL有什么问题？是不是有什么东西我没能逃脱？

Answer 1

sp_executesql只能执行单个TSQL批处理。因此，您必须分别执行每个批次，例如：

DECLARE @sql1 NVARCHAR(MAX);
DECLARE @sql2 NVARCHAR(MAX);
SET @sql1 = N'IF object_id(N''[DW.myview]'', ''V'') IS NOT NULL
DROP VIEW [DW.myview]';
set @sql2 = N'CREATE VIEW [DW.myview]  . . .'
EXECUTE sp_executesql @sql1;
EXECUTE sp_executesql @sql2;