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
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