Conditional ON in a stored procedure with OPENJSON-CodePudding

I have this stored procedure that inserts data into a table from an API string that is passed to the OPENJSON function. This API though, sometimes renders a different key name according to the length of the value.

For instance:

{
  {
   "id":"1",
   "shortName": Alex
  },
   "id":"2",
   "longName": Alexander
  }
}

Stored procedure:

CREATE PROCEDURE dbo.uspAddToTable 
    @json NVARCHAR(MAX)
AS
BEGIN
    INSERT INTO dbo.MyTable (id, name)
        SELECT id, name 
        FROM OPENJSON(@json) 
        WITH 
            (id integer '$.id',
             name varchar(100) '$.shortName' /* here how do I do: OR '$.longName' if '$.shortName' does not exist */
            ) tbl

Is this possible in the stored procedure to take one or the other key value name depending if it can't find the default shortName to longName?

CodePudding user response：

What you need to do, instead, is return both columns in your OPENJSON call, and then use COALESCE or ISNULL in your SELECT to return the non-NULL value:

CREATE PROCEDURE dbo.uspAddToTable @json nvarchar(MAX)
AS
BEGIN
    INSERT INTO dbo.MyTable (id,name)
    SELECT id,
           ISNULL(shortName,longName)
    FROM OPENJSON(@json)
            WITH (id int,
                  shortName varchar(100),
                  longName varchar(100)) OJ;
END;

I assume here that one of the other will be NULL or that shortName is the "prioritised" value.