SQL Server SELECT to JSON function
Starting from SQL Server 2016 you can use for json:
declare @t table(id int, name nvarchar(max), active bit)insert @t values (1, 'Bob Jones', 1), (2, 'John Smith', 0)select id, name, activefrom @tfor json autoWith older versions of SQL Server you can use for xml path, e.g.:
select '[' + STUFF(( select ',{"id":' + cast(id as varchar(max)) + ',"name":"' + name + '"' + ',"active":' + cast(active as varchar(max)) +'}' from @t t1 for xml path(''), type ).value('.', 'varchar(max)'), 1, 1, '') + ']'Output:
[{"id":1,"name":"Bob Jones","active":1},{"id":2,"name":"John Smith","active":0}]
So first off, I want to thank Kirill Polishchuk for the essential code sample ... thank you!
I took that and went to build a procedure to do what I need it to do, and that is to give me a JSON output based on "any" result set I want i.e. table object (not variable) in SQL Server.
Ideally, I would want this as a function, however due to limitations on what you can do within a function, that part will have to wait ... maybe v2. :)
And yes, registering an extended procedure (CLR) definitely be easier, but I wanted to avoid going that route for the time being.
PS: for temp tables, just put in 'tempdb..#tablename'
Here it is:
/* Author: Goran Biljetina Create date: 03/13/2013 Description: consume a table object (not table var), output it as JSON Properties string */ /* --> example run -- EXEC dbo.JSONreturn @tblObjNameFQ='[database].[schema].[object_name_table]'; */ CREATE PROCEDURE dbo.JSONreturn ( @committedRead bit = 0 --> if 1 then committed else uncommitted read ,@debugmode bit = 0 --> if 1 display certain outputs ,@tblObjNameFQ varchar(128) --> fully qualified table object name, i.e. db.schema.object_name ,@stringJSON nvarchar(max) = null OUTPUT ) AS BEGIN if @committedRead=0 begin SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; --> evaluate if necessary in test phase end else if @committedRead=1 begin SET TRANSACTION ISOLATION LEVEL READ COMMITTED; end SET NOCOUNT ON; ---------------------------------------------------------------------------------------------------------- if (PATINDEX('%[\.]%',@tblObjNameFQ)<1 AND patindex('%#%',@tblObjNameFQ)<1) OR LEN(@tblObjNameFQ)>(3*128) begin PRINT 'table (object) name not fully qualified or invalid!' RETURN -1 end declare @objname varchar(128) ,@dbname varchar(128) ,@schema varchar(128) ,@maxColNum int ,@inc int ,@dqsl_misc varchar(max) ,@dsql_wrapper varchar(max) ,@dsql_what varchar(max) ,@dsql_where varchar(max) ,@dsql_complete varchar(max) create table #maxColNum (column_id int) create table #ColPrep (colString varchar(max), column_id int) create table #JSONoutput (string nvarchar(max)) if patindex('%#%',@tblObjNameFQ)>0 begin set @objname = (PARSENAME(@tblObjNameFQ,1)) set @dbname = 'tempdb' end else if patindex('%#%',@tblObjNameFQ)<1 begin set @dbname = SUBSTRING(@tblObjNameFQ,1,PATINDEX('%[\.]%',@tblObjNameFQ)-1) set @objname = convert(varchar,(PARSENAME(@tblObjNameFQ,1))) set @schema = convert(varchar,(PARSENAME(@tblObjNameFQ,2))) end --select @objname[@objname], @dbname[@dbname], @schema[@schema] --select @dbname+'.'+@schema+'.'+@objname set @dqsl_misc = ' select max(column_id) from '+@dbname+'.sys.columns where object_id = (select object_id from '+@dbname+'.sys.objects where type = ''U'' and name like ''%'+@objname+'%'') ' insert into #maxColNum exec(@dqsl_misc) set @maxColNum = (select column_id from #maxColNum) set @dsql_what = '' set @dsql_wrapper = ' select ''['' + STUFF(( select '',{''+<<REPLACE>> +''}'' ' set @dsql_where = ' from '+@dbname+'.'+case when @schema is null then '' else @schema end+'.'+@objname+' t1 for xml path(''''), type ).value(''.'', ''varchar(max)''), 1, 1, '''') + '']'' ' set @dqsl_misc = ' select ''"''+sysc.name+''": '' +case when syst.name like ''%time%'' or syst.collationid is not null then ''"''''+cast(''+sysc.name+'' as varchar(max))+''''",'' when syst.name = ''bit'' then ''''''+cast((case when ''+sysc.name+''=1 then ''''true'''' else ''''false'''' end) as varchar(max))+'''','' else ''''''+cast(''+sysc.name+'' as varchar(max))+'''','' end as colString, sysc.column_id from '+@dbname+'.sys.columns sysc join '+@dbname+'.sys.systypes syst on sysc.system_type_id = syst.xtype and syst.xtype <> 240 and syst.name <> ''sysname'' where object_id = (select object_id from '+@dbname+'.sys.objects where type = ''U'' and name like ''%'+@objname+'%'') order by sysc.column_id ' insert into #ColPrep exec(@dqsl_misc) set @inc = (select MIN(column_id) from #ColPrep) while @inc<=@maxColNum begin set @dsql_what = @dsql_what+(select case when @inc = @maxColNum then replace(colString,',','') else colString end from #ColPrep where column_id = @inc) set @inc=@inc+1 IF @inc>@maxColNum set @dsql_what = ''''+@dsql_what+'''' IF @inc>@maxColNum BREAK ELSE CONTINUE end set @dsql_complete = REPLACE(@dsql_wrapper,'<<REPLACE>>',@dsql_what)+@dsql_where insert into #JSONoutput exec(@dsql_complete) SET @stringJSON = (Select string from #JSONoutput) ---------------------------------------------------------------------------------------------------------- END