MS SQL JSON类型type

在ms sql server 2016，已经支持json处理。

执行下面代码，将获取ms sql server对象类型以及其说明:

 

if object_id('tempdb.dbo.#json_type') is not null drop table #json_type

create table #json_type (    
    [type] tinyint,
    [data_type] nvarchar(30)
)
insert into  #json_type ([type],[data_type]) values 
(0,'null'),
(1,'string'),
(2,'int'),
(3,'true/false'),
(4,'array'),
(5,'object')

select [type],[data_type] from #json_type

 
把它写成一个自定义函数：

 

set ansi_nulls on
go
set quoted_identifier on
go

-- =============================================
-- author:      insus.net
-- create date: 2019-05-23
-- update date: 2019-05-23
-- description: 获取存储过程参数数据
-- =============================================
create or alter function [dbo].[svf_jsondatatype]
(
    @type tinyint
)
returns nvarchar(max)
as
begin
    return case
        when @type = 0 then 'null'
        when @type = 1 then 'string'
        when @type = 2 then 'int'
        when @type = 3 then 'true/false'
        when @type = 4 then 'array'
        when @type = 5 then 'object'
    end
end