Consider the below function which returns a portion of a well-formatted JSON object. It will return "Bad JSON" when the JSON is not well-formatted. Importantly, it will not error when the JSON is not well-formatted.
CREATE OR ALTER FUNCTION TestJSONParse
(
@nv nvarchar(max)
)
RETURNS TABLE
AS
RETURN
(
SELECT
v3 = isNull(t2.v2,t1.v1)
FROM (
SELECT
v1 = 'Bad JSON'
) AS t1
LEFT JOIN (
SELECT
v2 = JSON_VALUE(@nv,'$.prop')
WHERE ISJSON(@nv) <> 0
) AS t2
ON 1 = 1
);
GO
SELECT * FROM TestJSONParse('{"prop": "Good JSON"}');
SELECT * FROM TestJSONParse('zzzz');
GO
Results:
v3
Good JSON
v3
Bad JSON
So far, so good.
Now I want to use this function in a procedure and feed it the same bad JSON:
CREATE OR ALTER PROCEDURE TestJSONParseProc
(
@nv nvarchar(max)
)
AS
BEGIN
BEGIN TRY
DECLARE @v nvarchar(max);
SELECT
@v = v3
FROM TestJSONParse(@nv);
END TRY
BEGIN CATCH
SELECT
Result = N'I have caught an error.';
END CATCH
END
GO
EXEC TestJSONParseProc
@nv = N'zzzz';
GO
Results:
Msg 13609, Level 16, State 2, Procedure TestJSONParseProc, Line 10 [Batch Start Line 45]
JSON text is not properly formatted. Unexpected character 'z' is found at position 0.
Why does this error occur? The evaluation of JSON_VALUE should not take place because ISJSON should be 0 and therefore the row is filtered from the results.
Why isn't this error caught by the BEGIN TRY?
I found a suggestion online that this was some sort of a compilation error. Some support for that conclusion is provided by the behavior when the first call of the procedure after creation is with valid JSON:
CREATE OR ALTER PROCEDURE TestJSONParseProc
(
@nv nvarchar(max)
)
AS
BEGIN
BEGIN TRY
DECLARE @v nvarchar(max);
SELECT
@v = v3
FROM TestJSONParse(@nv);
SELECT v3 = @v;
END TRY
BEGIN CATCH
SELECT
Result = N'I have caught an error.';
END CATCH
END
GO
EXEC TestJSONParseProc
@nv = N'{"prop": "Good JSON"}';
EXEC TestJSONParseProc
@nv = N'zzzz';
GO
Results:
v3
Good JSON
v3
Bad JSON
Can anyone confirm that the reason this happens and why it can't be caught is because what's really happening is that SQL Server is evaluating the expression as part of the compilation process and therefore crashes before the error handling ever has a chance to work? (If so, is there any justification for this behavior?)
Or is there some other explanation?
For reference, @@VERSION returns:
Microsoft SQL Azure (RTM) - 12.0.2000.8
Feb 20 2021 17:51:58
Copyright (C) 2019 Microsoft Corporation
Update 2021-04-27:
Things which don't fix the issue:
(1) Wrapping the JSON_VALUE in a CASE statment:
CREATE OR ALTER FUNCTION TestJSONParse
(
@nv nvarchar(max)
)
RETURNS TABLE
AS
RETURN
(
SELECT
v3 = isNull(t2.v2,t1.v1)
FROM (
SELECT
v1 = 'Bad JSON'
) AS t1
LEFT JOIN (
SELECT
v2 = CASE WHEN ISJSON(@nv) <> 0 THEN JSON_VALUE(@nv,'$.prop') ELSE NULL END
WHERE ISJSON(@nv) <> 0
) AS t2
ON 1 = 1
);
GO
(2a) "Touching" the parameter value to make it "different" - ISNULL
CREATE OR ALTER PROCEDURE TestJSONParseProc
(
@nv nvarchar(max)
)
AS
BEGIN
BEGIN TRY
DECLARE @v nvarchar(max);
SET @nv = isNull(@nv,N'');
SET @nv2 = @nv;
SELECT
@v = v3
FROM TestJSONParse(@nv);
SELECT v3 = @v;
END TRY
BEGIN CATCH
SELECT
Result = N'I have caught an error.';
END CATCH
END
GO
(2b) "Touching" the parameter value to make it "different" - concatenate empty string
CREATE OR ALTER PROCEDURE TestJSONParseProc
(
@nv nvarchar(max)
)
AS
BEGIN
BEGIN TRY
DECLARE @v nvarchar(max);
SET @nv = @nv + N'';
SELECT
@v = v3
FROM TestJSONParse(@nv);
SELECT v3 = @v;
END TRY
BEGIN CATCH
SELECT
Result = N'I have caught an error.';
END CATCH
END
GO
(3) Wrapping entire function call in ISJSON check
CREATE OR ALTER PROCEDURE TestJSONParseProc
(
@nv nvarchar(max)
)
AS
BEGIN
BEGIN TRY
DECLARE @v nvarchar(max);
IF ISJSON(@nv) <> 0
BEGIN
SELECT
@v = v3
FROM TestJSONParse(@nv);
END
ELSE
BEGIN
RAISERROR('Bad JSON',15,1);
END
SELECT v3 = @v;
END TRY
BEGIN CATCH
SELECT
Result = N'I have caught an error.';
END CATCH
END
GO
Declaring a new variable in the proc and using that does, however, fix the issue:
CREATE OR ALTER PROCEDURE TestJSONParseProc
(
@nv nvarchar(max)
)
AS
BEGIN
BEGIN TRY
DECLARE @v nvarchar(max);
DECLARE @nv2 nvarchar(max);
SET @nv2 = @nv;
SELECT
@v = v3
FROM TestJSONParse(@nv2);
SELECT v3 = @v;
END TRY
BEGIN CATCH
SELECT
Result = N'I have caught an error.';
END CATCH
END
GO
That also bolsters the case that it's some sort of parameter sniffing compilation issue.
Update 2021-04-28:
As Mikael Eriksson suggested in a comment, using OPTIMIZE FOR @nv = NULL works as well. As noted, this also bolsters the case for this being a compile-time issue.
If a set of valid objects which would run successfully won't compile because of how the optimizer evaluates them, I would argue that's a bug in the optimizer/compiler.
CREATE OR ALTER PROCEDURE TestJSONParseProc
(
@nv nvarchar(max)
)
AS
BEGIN
BEGIN TRY
DECLARE @v nvarchar(max);
SELECT
@v = v3
FROM TestJSONParse(@nv)
OPTION (OPTIMIZE FOR (@nv = NULL));
SELECT v3 = @v;
END TRY
BEGIN CATCH
SELECT
Result = N'I have caught an error.';
END CATCH
END
GO
option (optimize for (@nv = null))
to the same effect. It do suggest that your own conclusion is correct that the error happens before exectution.