CODEWITHSUNDEEP
jsonsql-serverstored-procedures
Infin8Loop
Infin8Loop

Reputation: 301

In SQL Server can I switch JSON output on and off via a parameter?

I tried the following code but I am getting a syntax error:

ORDER BY 
    Date ASC
IF (@Format = 'JSON')
    FOR JSON AUTO

Is it possible to do this? I am trying to control whether the output of this stored procedure is a table or a JSON document via an input parameter.

Upvotes: 2

Views: 113

Answers (1)

Andrea
Andrea

Reputation: 12395

Solution 1

To dynamically alter your query you can use dynamic TSQL:

create table #tmp ([Date] datetime2)
insert into #tmp values
 ('2018-04-01')
,('2018-04-11')
,('2018-03-21')
,('2018-06-01')
,('2018-07-01')

declare @Format varchar(50)= 'JSON'
declare @sql   nvarchar(max)= ''

set @sql += ' select * from #tmp '
set @sql += ' ORDER BY '
set @sql += ' Date ASC'

if (@Format = 'JSON')
    set @sql += ' FOR JSON AUTO'

exec (@sql)

Result:

enter image description here

Now if you execute again the code changing the value of @Format variable:

declare @Format varchar(50)= 'other'

Result:

enter image description here

Solution 2

Another solution is to call different stored procedures depending on the value of your variable:

if (@Format = 'JSON')
    exec dbo.My_SP_that_Returns_JSON
else
    exec dbo.My_SP_that_Returns_a_table

Upvotes: 1

Related Questions