将资料表的资料转成Json抛出成API用

在MSSQL2016以下版本没有Json函数可用@@..
那就自己写一个来汇出成API在用的Json格式
首先先建立测试用的资料表以及自订Json的跳脱字元的转换~

CREATE TABLE [Test](
	[Test_ID] [int] NOT NULL,
	[Test_Name] [nvarchar](50) NULL,
	[Test_Sort] [int] NULL default 0,
	[Test_Create_Date] [datetime] NULL,
 CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED 
(
	[Test_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

go

Create function [dbo].[Json_Fun](@Str nvarchar(max))
returns nvarchar(max)
as
begin
	set @Str = Replace(@Str,'\','\\')
	set @Str = Replace(@Str,'"','\"')
	set @Str = Replace(@Str,char(13) + char(10),'\n')
	set @Str = Replace(@Str,char(13),'\n')
	set @Str = Replace(@Str,char(9),'　')
	return @Str
end

go

在来就是在Test资料表先建立几笔测试资料~
然後用T-Sql写转换成Json过程汇出资料~

declare @TableName nvarchar(50) = N'Test'

declare @Sql nvarchar(max) = ''
declare @i int = 0,@Count int = 0,@ColumnName nvarchar(50) = '',@ColminType nvarchar(50) = ''

SELECT @Count=Count(0)
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = @TableName

set @Sql += 'select ''['' + Convert(nvarchar(max)'
set @Sql += ' ,stuff(('
set @i = 0
while(@i<@Count)
begin
	set @i = @i + 1
	select @ColumnName=COLUMN_NAME
	,@ColminType=DATA_TYPE
	from INFORMATION_SCHEMA.COLUMNS 
	where TABLE_NAME = @TableName
	and ORDINAL_POSITION = @i

	if(@i=1)
	begin
		if(@ColminType in ('bigint','numeric','bit','smallint','decimal','smallmoney','int','tinyint','money','float','real'))
		begin
			set @Sql += ' select '',{"' + @ColumnName + '":"'' + Convert(varchar,' + @ColumnName + ') + ''"'''
		end
		else if(@ColminType in('date','datetimeoffset','datetime2','smalldatetime','datetime','time'))
		begin
			set @Sql += ' select '',{"' + @ColumnName + '":"'' + Convert(varchar,' + @ColumnName + ',120) + ''"'''
		end
		else if(@ColminType in('char','varchar','text','nchar','nvarchar','ntext'))
		begin
			set @Sql += ' select '',{"' + @ColumnName + '":"'' + dbo.Json_Fun(isNull(' + @ColumnName + ','''')) + ''"'''
		end
		else
		begin
			set @Sql += ' select '',{"' + @ColumnName + '":"'' + Convert(varchar,' + @ColumnName + ') + ''"'''
		end
	end
	else
	begin
		if(@ColminType in ('bigint','numeric','bit','smallint','decimal','smallmoney','int','tinyint','money','float','real'))
		begin
			set @Sql += ' + '',"' + @ColumnName + '":"'' + Convert(varchar,' + @ColumnName + ') + ''"'''
		end
		else if(@ColminType in('date','datetimeoffset','datetime2','smalldatetime','datetime','time'))
		begin
			set @Sql += ' + '',"' + @ColumnName + '":"'' + Convert(varchar,' + @ColumnName + ',120) + ''"'''
		end
		else if(@ColminType in('char','varchar','text','nchar','nvarchar','ntext'))
		begin
			set @Sql += ' + '',"' + @ColumnName + '":"'' + dbo.Json_Fun(isNull(' + @ColumnName + ','''')) + ''"'''
		end
		else
		begin
			set @Sql += ' + '',"' + @ColumnName + '":"'' + Convert(varchar,' + @ColumnName + ') + ''"'''
		end

	end
end
set @Sql += ' + ''}'''
set @Sql += ' from (select * from ' + @TableName + ') as k'
set @Sql += ' for xml path('''')'
set @Sql += ' ),1,1,'''')'
set @Sql += ') + '']'' as JsonString   '

exec sp_executesql @Sql

其实他的SQL会被转成这样的SQL语句查询

select '[' + Convert(nvarchar(max) ,stuff(( select ',{"Test_ID":"' + Convert(varchar,Test_ID) + '"' + ',"Test_Name":"' + dbo.Json_Fun(isNull(Test_Name,'')) + '"' + ',"Test_Sort":"' + Convert(varchar,Test_Sort) + '"' + ',"Test_Create_Date":"' + Convert(varchar,Test_Create_Date,120) + '"' + '}' from (select * from Test) as k for xml path('') ),1,1,'')) + ']' as JsonString   

经过组建SQL後，用sp_executesql查询资料~得到Json资料如下~

[{"Test_ID":"1","Test_Name":"Test","Test_Sort":"1","Test_Create_Date":"2021-05-08 12:36:00"},{"Test_ID":"2","Test_Name":"Show","Test_Sort":"2","Test_Create_Date":"2021-10-07 00:00:00"}]

可以先到Json平台验证你的资料是否正确~若错误你在看跳脱字元哪个要补上或其他问题修正~
http://json.parser.online.fr/

正常如下~