CODEWITHSUNDEEP
jsonsql-serversql-server-json
ddrjca
ddrjca

Reputation: 482

Conditionally using FOR JSON PATH in stored procedure

I would like to create stored procedures that conditionally output JSON based on an input parameter. Can this be done without having to duplicate the select statement? 

CREATE PROCEDURE myProcedure @outputJson bit 
AS
BEGIN

        IF outputJson = 1
        BEGIN

            SELECT col1,
                   col2,
                   col3
              FROM dbo.MyTable 
               FOR JSON PATH    

        END
        ELSE
        BEGIN

            SELECT col1,
                   col2,
                   col3
              FROM dbo.MyTable 

        END

END
GO

Upvotes: 1

Views: 897

Answers (1)

Hector Montero
Hector Montero

Reputation: 221

You can use use dynamic sql to include the "FOR JSON" clause based in the parameter.

CREATE PROCEDURE dbo.myProcedure @outputJson bit
AS
BEGIN
    DECLARE @sql nvarchar(max) = N'SELECT col1, col2, col3 FROM dbo.MyTable';

    IF @outputJson = 1
    BEGIN
        SET @sql += ' FOR JSON PATH';
    END

    EXECUTE sp_executesql @sql;
END
GO

Upvotes: 2

Related Questions