On-demand training: More Gems from the SQL Prompt Treasure Chest. Watch now.

How can I prevent SqlPrompt to Pascalize "Value" for OPENJSON

We are using a Case Sensitive database. RedGate Prompt Format SQL always Pascalize value keyword and turn it into "Value" so the procedure can not be compiled on case-sensitive databases.

Example: The result of FormatSQL on a case-sensitive Database

DECLARE @Json NVARCHAR(MAX) = '[{"ID":"1", "NAME": "JOHN"},{"ID":"2", "NAME": "SARA"}]';
SELECT JSON_VALUE(OJ.Value, '$.ID') AS ID, JSON_VALUE(OJ.Value, '$.NAME') AS NAME
FROM OPENJSON(@Json) AS OJ;

The value must be in lowercase but FormatSQL make it to Pascal case.
How can we prevent this?

Answers

  • @madnik7 I would think the only way to keep this from occurring is to use the feature that was added in SQL Prompt a few versions back that allows for a special comment to be used to force SQL Prompt to not format certain text. The comment is as follows:

    -- SQL Prompt formatting off
    <Whatever is between the comments lines won't be formatted
    -- SQL Prompt formatting on

    Other than that, I think the only solution would involve development. You could definitely post a suggestion on the SQL Prompt UserVoice here:

    https://redgate.uservoice.com/forums/94413-sql-prompt

    I hope that helps.

    --Adam Hafner
Sign In or Register to comment.