asked on

StoredProcedure to JSON query faulty syntax

I have the following Stored Procedure which returns data in JSON format, but the syntax is incorrect. Perhaps someone can help check on this ?

ALTER PROCEDURE [dbo].[api_cc_one_active_contact] 
  @userid INT
AS
    DECLARE @json_result [NVARCHAR](MAX) = ''

SELECT '{""first_name"": ""' + FirstNm + '"",""last_name"": ""' + LastNm + '"",""email_addresses"":[{""email_address"": ""' + email + '""}]}' as json_result FROM dbo.Users WHERE userid = @userid

Open in new window

The result is:

 {""first_name"": ""Marlo"",""last_name"": ""Doe"",""email_addresses"":[{""email_address"": ""marlo@domain.com""}]}

Open in new window

The error I get when validating the JSON is:

Error: Parse error on line 1:
 {""first_name"": ""Marl
----^
Expecting 'EOF', '}', ':', ',', ']', got 'undefined'

Open in new window

ASKER CERTIFIED SOLUTION

Nitin Sontakke

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aleks

ASKER

Not sure what happened there, thanks for the observation, this worked:

ALTER PROCEDURE [dbo].[api_cc_one_active_contact] 
  @userid INT
AS
    DECLARE @json_result [NVARCHAR](MAX) = ''

SELECT '{"first_name": "' + FirstNm + '","last_name": "' + LastNm + '","email_addresses":[{"email_address": "' + email + '"}]}' as json_result FROM dbo.Users WHERE userid = @userid

Open in new window