jsonsql-serversql-server-json

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

Solution

  • 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