Format SQL fails when using INSERT INTO ... EXECUTE

I'm currently running SQL Prompt 8.0.8.2086 but have been having this problem for many weeks, also on previous versions, until I finally figured out what was triggering it yesterday.

I've had a set of queries that when you run Format SQL it errors out usually with the message of:
"Error laying out sql
SQL Prompt was unable to complete this operation.
Problem areas have been highlighted."

Usually, the highlight is on the first character of the first line and says:
"Could not find parent of type Microsoft.SQLServer.TransactSql.ScriptDom.ExecuteStatement"

Here is a simplified version of the SQL code that's causing the problem.
DECLARE @StartDate AS DATE = '2017-09-01';
DECLARE @EndDate AS DATE = '2017-09-15';

DECLARE @MonthsBack AS INT = 6;
DECLARE @SafetyPlanStart DATE;

SET @SafetyPlanStart = DATEADD(MONTH, -@MonthsBack, @StartDate);

IF OBJECT_ID('tempdb..#safetyplans') IS NOT NULL
	DROP TABLE #safetyplans;

CREATE TABLE #safetyplans
	(
	MRN INT,
	DocumentDate DATE--,
	--DateStored DATE,
	);

INSERT INTO #safetyplans
	(
	MRN,
	DocumentDate--,
	--DateStored
	)
EXECUTE (
	N'SELECT
		safetyplans.MRN,
		safetyplans.DocumentDate--,
		--safetyplans.DateStored
	FROM
		OBPRD.dbo.SafetyPlansByDates (?, ?, ?, ?) safetyplans', @SafetyPlanStart, @EndDate, NULL, NULL) AT [OBDB\OBPROD];

SELECT	* FROM	#safetyplans;

If I comment out the INSERT INTO portion up to but not including EXECUTE then it will format without an issue.
Tagged:

Best Answer

  • James RJames R Posts: 104 Silver 4
    Hi @kenneyh ,

    Thanks for your post! We've managed to reproduce this issue here and have come up with a fix - hopefully it will be released in the middle of next week :)

    Regards,
    James
    Software Engineer
    Redgate Software

Answers

Sign In or Register to comment.