SQL Prompt formatting fails if a MERGE statement contains a "between"-clause in the NOT MATCHED part

ralf

Hi!
I found a Problem when trying to Format a MERGE Statement.

DECLARE @Param1 INT;
DECLARE @Param2 INT;
DECLARE @Param3 BIT;

DECLARE @T TABLE
(
[Col1] INT
, [Col2] INT
, [Col3] INT
);


----Uncomment this to see the problem scenario
----seems that the "AND ( [target].[Col1] BETWEEN @Param1 AND @Param2)" statement is causing the problem
----#####################################################################################
--MERGE INTO @T AS [target]
--USING (
-- SELECT 1 AS [Col1]
-- , 2 AS [Col2]
-- , 3 AS [Col3]
-- ) AS [source] ( [Col1], [Col2], [Col3] )
--ON [source].[Col1] = [target].[Col1]
--WHEN MATCHED THEN UPDATE SET [target].[Col1] = [source].[Col1]
-- , [target].[Col2] = [source].[Col2]
-- , [target].[Col3] = [source].[Col3]
--WHEN NOT MATCHED BY SOURCE AND ( @Param3 = 1 )
--AND ( [target].[Col1] BETWEEN @Param1 AND @Param2)
--THEN DELETE;
----#####################################################################################



----Uncomment this to the the bypass solution with the "IF ( 1=1 ) BEGIn END" statement
----#####################################################################################
--IF ( 1 = 1 )
-- BEGIN
-- MERGE INTO @T AS [target]
-- USING (
-- SELECT 1 AS [Col1]
-- , 2 AS [Col2]
-- , 3 AS [Col3]
-- ) AS [source] ( [Col1], [Col2], [Col3] )
-- ON [source].[Col1] = [target].[Col1]
-- WHEN MATCHED THEN UPDATE SET [target].[Col1] = [source].[Col1]
-- , [target].[Col2] = [source].[Col2]
-- , [target].[Col3] = [source].[Col3]
-- WHEN NOT MATCHED BY SOURCE AND ( @Param3 = 1 )
-- AND ( [target].[Col1]
-- BETWEEN @Param1 AND @Param2
-- ) THEN DELETE;


-- END;
----#####################################################################################


The first MERGE Statement is failing, obviously caused be the "AND ( [target].[Col1] BETWEEN @Param1 AND @Param2)" part. If you comment this out, the formatting is working.
I've found a way to bypass the problem. If you encapsulate the complete MERGE Statement in a "IF ( 1 = 1 ) BEGIN ...END" Statement the formatting is not failing (but the Output is awful ;-) )
I'm currently working with SQL Prompt 8.0.0.1241

Best regards
Ralf

Russell D

Thanks @Ralf, we're investigating this now.

James R

Hi @Ralf, Just letting you know that we've found a couple of issues with BETWEEN statements recently, we've got a fix here and should have it released in the next few days

Thanks,