0

I'm fiddling around with JSON and SQL, and I need to update a record based on a new JSON string.

Here's what I've got, but to be honest, It seems like an awful lot of code:

    create procedure proc_alter_contact (@json nvarchar(max))

    as

    with new_values as (select * from 
            openjson(@json)
            WITH
            (
            email varchar(255)          '$.Email',
            Prefix varchar(10)          '$.Prefix',
            FirstName varchar(255)      '$.FirstName',
            MiddleInitial varchar(10)   '$.MiddleInitial',
            LastName varchar(255)       '$.LastName',
            Suffix varchar(10)          '$.Suffix',
            Title varchar(255)          '$.Title',
            Company varchar(255)        '$.Company',
            Address1 varchar(255)       '$.Address1',
            Address2 varchar(255)       '$.Address2',
            City varchar(255)           '$.City',
            State varchar(10)           '$.State',
            Zip varchar(5)              '$.Zip',
            Zip_4 varchar(4)            '$.Zip4',
            Phone varchar(55)           '$.Phone',
            Extension Varchar(55)       '$.Extension',
            Unregistered bit            '$.Unregistered'
        ))


    update t1
    set 
    t1.email            = t2.email,
    t1.prefix           = t2.prefix,
    t1.FirstName        = t2.FirstName,
    t1.MiddleInitial    = t2.MiddleInitial,
    t1.LastName         = t2.LastName,
    t1.Suffix           = t2.Suffix,
    t1.Title            = t2.Title,
    t1.Company          = t2.Company,
    t1.Address1         = t2.Address1,
    t1.Address2         = t2.Address2,
    t1.City             = t2.City,
    t1.State            = t2.State,
    t1.Zip              = t2.Zip,
    t1.Zip_4            = t2.Zip_4,
    t1.Phone            = t2.Phone,
    t1.Extension        = t2.Extension,
    t1.Unregistered     = t2.Unregistered
    FROM announcement_contact t1
    inner join new_values t2 on t1.email = t2.email;

here's an example JSON string:

    exec proc_alter_contact

    @json =
    '{
    "Email": "WMogh@starfleet.gov",
    "Prefix":null,
    "FirstName": "Worf",
    "MiddleInitial": "",
    "LastName": "Mogh",
    "Suffix": "Son Of",
    "Title" :"LT Commander",
    "Company": "Starfleet",
    "Address1": "1 Academey Drive",
    "Address2": "suite 1701",
    "City": "San Francisco",
    "State": "CA",
    "Zip" :"59312",
    "Zip_4": "9577",
    "Phone": "5469781769",
    "Extension":"9",
    "Unregistered": 0
    }' 

Question Is there are more efficient, or more sensible way to do this?

  • 1
    The only way for it to be less code, I think, is to make it dynamic SQL. But that won't be much less code, and it will still be around the same efficiency because you need to end up with roughly the same query anyway. If you want it to be less SQL code, you could break out the JSON into its individual parts in your application code before calling the procedure, I suppose, and pass them as individual parameters. But even that doesn't make the code that much smaller, it just moves the names around. Also, have you tried this code? openjson() won't work in SQL Server 2008 R2. – Aaron Bertrand Jun 27 at 17:59
  • @AaronBertrand yea, I might do that, but it seems like a different kind of overkill. and oh, this server is Azure! I'll update the tags. I'm also not sure what the best practice is when they have to change their email... – James Jun 27 at 18:07
  • If you think that's a lot of code, try doing the same thing with XML. Don't think it's gonna get much tidier than this, to be honest. – Erik Darling Jun 27 at 21:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.