SET FMTONLY ON/OFF is deprecated according to the docs and should be replaced by sp_describe_first_result_set. But sp_describe_first_result_set returns 'incorrect syntax' message on correct queries. How to correctly migrate?

Question

Using this simple table structure:

CREATE TABLE Persoon_ (

ID INT,

Adres NVARCHAR(50)

)

CREATE TABLE Factuur_ (

ID INT,

Debiteur INT,

Persoon INT,

Bedrijf INT,

Derde INT

)

CREATE TABLE Bedrijf_ (

ID INT,

FactuurAdres NVARCHAR(50),

PostAdres NVARCHAR(50),

BezoekAdres NVARCHAR(50)

)

This works OK

;SET FMTONLY ON

SELECT

(CASE

WHEN Factuur.Debiteur = 1

THEN Persoon.Adres

ELSE (CASE

WHEN ((B.FactuurAdres IS NULL) OR (B.FactuurAdres = ''))

THEN (CASE

WHEN ((B.Postadres IS NULL) OR (B.Postadres = ''))

THEN B.Bezoekadres

ELSE B.Postadres

END)

ELSE B.FactuurAdres

END)

END) AS DebiteurAdres

FROM Factuur_ Factuur

INNER JOIN Persoon_ Persoon ON Factuur.Persoon = PErsoon.ID

LEFT JOIN Bedrijf_ B ON (Factuur.Debiteur = 2 AND B.Id = Factuur.Bedrijf) OR

(Factuur.Debiteur = 3 AND B.Id = Factuur.Derde)

; SET FMTONLY OFF

But this gives a syntax error

EXEC sp_describe_first_result_set @tsql =

N'

SELECT

(CASE

WHEN Factuur_.Debiteur = 1

THEN Persoon_.Adres

ELSE (CASE

WHEN ((B.FactuurAdres IS NULL) OR (B.FactuurAdres = ''))

THEN (CASE

WHEN ((B.Postadres IS NULL) OR (B.Postadres = ''))

THEN B.Bezoekadres

ELSE B.Postadres

END)

ELSE B.FactuurAdres

END)

END) AS DebiteurAdres

FROM Factuur_

INNER JOIN Persoon_ ON Factuur_.Persoon = PErsoon_.ID

LEFT JOIN Bedrijf_ B ON (Factuur_.Debiteur = 2 AND B.Id = Factuur_.Bedrijf) OR

(Factuur_.Debiteur = 3 AND B.Id = Factuur_.Derde)

'

How to correctly migrate away from SET FMTONLY ON/OFF ?

(SQL Server version: Microsoft SQL Server 2019 (RTM-CU28-GDR) (KB5042749) - 15.0.4390.2 (X64) Aug 12 2024 13:08:42 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor) )

Answer 1

Your SQL batch includes single quotes. When you include the SQL batch in a string, you need to double these, or else the first single quote acts as the end of the SQL string. And the rest is just a syntax error.

Change

WHEN ((B.FactuurAdres IS NULL) OR (B.FactuurAdres = ''))
THEN (CASE
WHEN ((B.Postadres IS NULL) OR (B.Postadres = ''))

to

WHEN ((B.FactuurAdres IS NULL) OR (B.FactuurAdres = ''''))
THEN (CASE
WHEN ((B.Postadres IS NULL) OR (B.Postadres = ''''))