SQL: How to add string text to my CTE output

I’ve a SQL question. I want to add a text value to the front of the JSON result of my view. I am now able to display the results of my view as JSON and then return with the “SELECT * FROM @TABLE” as the result.

I need this return query for my ETL process.

The output of my CTE now is:

{"SystemType":"XX","SourceSystemName":"XX"}

The result I want to have is:

,"pipelineParameters":{"SystemType":"XX","SourceSystemName":"XX"}

What I’ve now:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER   PROCEDURE [Schema].[StoredProcedure]
AS
SET NOCOUNT ON;
        BEGIN

            DECLARE @TABLE TABLE(RESULT NVARCHAR(MAX))
            DECLARE @QUERY NVARCHAR(MAX) = '

            ;WITH x(Name) as 
            (
            SELECT * FROM [Schema].[View] FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
            )
            Select * from x  
            '
            Insert @TABLE
            EXEC (@QUERY)
            Select * from @TABLE;

        END
GO

Answer

I might be oversimplifying this, but why not just concatenate the beginning of the string to your resulting column?

That is, replace the final:

select * from @TABLE;

With:

select ',"pipelineParameters":' + name from @TABLE

Advertisement

Answer