Format SQL fails when using INSERT INTO ... EXECUTE

kenneyh

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.

James R

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

FredericoJ

Hello @kenneyh,

We've fixed this issue in our latest version of SQL Prompt 8.2.1.2603 (installer here).

You can find more information about this release in this forum post.

Kind regards,

Frederico