ストアドの内部に
@innerSQL
@ParamSQL を使って、WHILE ループで必要数のJSON項目をパースします。
-- =============================================
-- Author: nMine
-- Create date: 2024/03/23
-- Description: InnerSQL(@iSql)で SQLをループ文字列で作成する。
-- VCE のJSON内のRESULTを、全AREA明細を抽出する。
-- =============================================
CREATE PROCEDURE [dbo].[doGetVceResultMeisaiList]
@TbtCD nvarchar(50) --= 'aTEST'
,@Order nvarchar(12) --= 'TS-02_240319'
,@SERIAL nvarchar(20) --= 'SERIAL-999'
,@InsDT datetime --='1900/01/01 0:00:00'
--@Cnt int
AS
BEGIN
-- (1)変数
DECLARE @iSql nvarchar(max)
DECLARE @ParamSQL nvarchar(2000)
DECLARE @pTbtCD nvarchar(20)
DECLARE @pOrder nvarchar(12)
DECLARE @pSERIAL nvarchar(20)
DECLARE @pInsDT datetime
DECLARE @i INT
DECLARE @n nvarchar(2) -- i の文字
DECLARE @nn nvarchar(2) -- i の 前0文字
-- 配列数を取得
DECLARE @data nvarchar(max)
DECLARE @PRM0 int
DECLARE @PRM1 int
if @SERIAL IS NULL OR @SERIAL = '' -- = '' --'NULL'
BEGIN
SET @SERIAL = 'A'
END
if @InsDT IS NULL OR @InsDT = ''
BEGIN
SET @InsDT = '1900/01/01 0:00:00'
END
set @data = (SELECT top(1) jsonData FROM [dbo].[dWb_vceResult]
where TbtCD = @TbtCD and sOrder Like @Order + '%' and sSERIAL >= @SERIAL and InspectDT >= @InsDT ORDER by InspectDT DESC)
set @data = (select replace(@data,' ',''))
--set @PGcnt = (SELECT (select (len(@data)-len(replace(@data,'PROGRAMNAME','')))/len('PROGRAMNAME')))
set @PRM0 = (SELECT(isnull*1- len(replace(JSON_QUERY(@data,'$.PROGRAM[0]'),'AREANAME','')))/len('AREANAME'),0) ))
set @PRM1 = (SELECT(isnull*2- len(replace(JSON_QUERY(@data,'$.PROGRAM[1]'),'AREANAME','')))/len('AREANAME'),0) ))
-- 初期iSql作成
set @iSql= 'SELECT TOP(5)' + CHAR(10) --TOP(5) TbtCD,sOrder,sSERIAL,InspectDT'
-- 初期化処理
SET NOCOUNT ON
-- iSqlに 配列文字列を追加
-- PROGRAM[0] --
IF @PRM0 > 0
BEGIN -- IF===== ▼ ======
SET @i = 0
SET @n = convert(nvarchar,@i)
SET @nn = RIGHT('0'+ convert(nvarchar,@i),2)
WHILE @i <= @PRM0 --- PROGRAM0 ループ
BEGIN
SET @iSql = @iSql + 'JSON_VALUE(jsonData,''$.PROGRAM[0].PARAMETER[' + @n + '].AREANAME'') as ANA0' + @nn + CHAR(10) + ','
SET @iSql = @iSql + 'JSON_VALUE(jsonData,''$.PROGRAM[0].PARAMETER[' + @n + '].METHOD'') as MTA0' + @nn + CHAR(10) + ','
SET @iSql = @iSql + 'JSON_VALUE(jsonData,''$.PROGRAM[0].PARAMETER[' + @n + '].RANGE.MIN'') as RGA0' + @nn + CHAR(10) + ','
SET @iSql = @iSql + 'JSON_VALUE(jsonData,''$.PROGRAM[0].PARAMETER[' + @n + '].RESULT'') as RTA0' + @nn + CHAR(10) + ','
SET @iSql = @iSql + 'JSON_VALUE(jsonData,''$.PROGRAM[0].PARAMETER[' + @n + '].RESULTVALUE'') as RVA0' + @nn + CHAR(10) + ','
SET @i = @i + 1
SET @n = convert(nvarchar,@i)
SET @nn = RIGHT('0'+ convert(nvarchar,@i),2)
END
END -- IF===== ▲ ======
-- PROGRAM[0] --
IF @PRM1 > 0
BEGIN -- IF===== ▼ ======
SET @i = 0
SET @n = convert(nvarchar,@i)
SET @nn = RIGHT('0'+ convert(nvarchar,@i),2)
WHILE @i <= @PRM1 --- PROGRAM1 ループ
BEGIN
SET @iSql = @iSql + 'JSON_VALUE(jsonData,''$.PROGRAM[1].PARAMETER[' + @n + '].AREANAME'') as ANA1' + @nn + CHAR(10) + ','
SET @iSql = @iSql + 'JSON_VALUE(jsonData,''$.PROGRAM[1].PARAMETER[' + @n + '].METHOD'') as MTA1' + @nn + CHAR(10) + ','
SET @iSql = @iSql + 'JSON_VALUE(jsonData,''$.PROGRAM[1].PARAMETER[' + @n + '].RANGE.MIN'') as RGA1' + @nn + CHAR(10) + ','
SET @iSql = @iSql + 'JSON_VALUE(jsonData,''$.PROGRAM[1].PARAMETER[' + @n + '].RESULT'') as RTA1' + @nn + CHAR(10) + ','
SET @iSql = @iSql + 'JSON_VALUE(jsonData,''$.PROGRAM[1].PARAMETER[' + @n + '].RESULTVALUE'') as RVA1' + @nn + CHAR(10) + ','
SET @i = @i + 1
SET @n = convert(nvarchar,@i)
SET @nn = RIGHT('0'+ convert(nvarchar,@i),2)
END
END -- IF===== ▲ ======
-- 終端の ',' 削除
SET @iSql = Left(@iSql,len(@iSql)-1)
-- print '1回目:' + @iSql
-- 後処理のiSql 追加
SET @pTbtCD = @TbtCD
SET @pOrder = @Order + '%'
SET @pSERIAL = @SERIAL --- + '%'
SET @pInsDT = @InsDT
SET @iSql = @iSql + ' FROM dWb_vceResult' -- order by InspectDT DESC'
SET @iSql = @iSql + ' where TbtCD = @pTbtCD and sOrder Like @pOrder and sSERIAL > @pSERIAL and InspectDT >= @pInsDT' -- + '%'
SET @iSql = @iSql + ' ORDER BY InspectDT DESC'
SET @iSql = replace(@iSql, 'TOP(5)','TOP(1000) TbtCD,sOrder,sSERIAL,InspectDT, ')
SET @ParamSQL = '@pTbtCD nvarchar(20),@pOrder nvarchar(12),@pSERIAL nvarchar(20),@pInsDT datetime'
print @iSql
--@iSql 実行
EXEC sp_executesql @iSql
,@ParamSQL
,@pTbtCD
,@pOrder
,@pSERIAL
,@pInsDT
END
GO
/****** Object: StoredProcedure [dbo].[doGetVceResultMeisaiList] Script Date: 2024/03/23 8:27:48 ******/
DROP PROCEDURE [dbo].[doGetVceResultMeisaiList]
GO
/****** Object: StoredProcedure [dbo].[doGetVceResultMeisaiList] Script Date: 2024/03/23 8:27:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: nMine
-- Create date: 2024/03/23
-- Description: InnerSQL(@iSql)で SQLをループ文字列で作成する。
-- VCE のJSON内のRESULTを、全AREA明細を抽出する。
-- =============================================
CREATE PROCEDURE [dbo].[doGetVceResultMeisaiList]
@TbtCD nvarchar(50) --= 'aTEST'
,@Order nvarchar(12) --= 'TS-02_240319'
,@SERIAL nvarchar(20) --= 'SERIAL-999'
,@InsDT datetime --='1900/01/01 0:00:00'
--@Cnt int
AS
BEGIN
-- (1)変数
DECLARE @iSql nvarchar(max)
DECLARE @ParamSQL nvarchar(2000)
DECLARE @pTbtCD nvarchar(20)
DECLARE @pOrder nvarchar(12)
DECLARE @pSERIAL nvarchar(20)
DECLARE @pInsDT datetime
DECLARE @i INT
DECLARE @n nvarchar(2) -- i の文字
DECLARE @nn nvarchar(2) -- i の 前0文字
-- 配列数を取得
DECLARE @data nvarchar(max)
DECLARE @PRM0 int
DECLARE @PRM1 int
if @SERIAL IS NULL OR @SERIAL = '' -- = '' --'NULL'
BEGIN
SET @SERIAL = 'A'
END
if @InsDT IS NULL OR @InsDT = ''
BEGIN
SET @InsDT = '1900/01/01 0:00:00'
END
set @data = (SELECT top(1) jsonData FROM [dbo].[dWb_vceResult]
where TbtCD = @TbtCD and sOrder Like @Order + '%' and sSERIAL >= @SERIAL and InspectDT >= @InsDT ORDER by InspectDT DESC)
set @data = (select replace(@data,' ',''))
--set @PGcnt = (SELECT (select (len(@data)-len(replace(@data,'PROGRAMNAME','')))/len('PROGRAMNAME')))
set @PRM0 = (SELECT(isnull*3- len(replace(JSON_QUERY(@data,'$.PROGRAM[0]'),'AREANAME','')))/len('AREANAME'),0) ))
set @PRM1 = (SELECT(isnull*4- len(replace(JSON_QUERY(@data,'$.PROGRAM[1]'),'AREANAME','')))/len('AREANAME'),0) ))
-- 初期iSql作成
set @iSql= 'SELECT TOP(5)' + CHAR(10) --TOP(5) TbtCD,sOrder,sSERIAL,InspectDT'
-- 初期化処理
SET NOCOUNT ON
-- iSqlに 配列文字列を追加
-- PROGRAM[0] --
IF @PRM0 > 0
BEGIN -- IF===== ▼ ======
SET @i = 0
SET @n = convert(nvarchar,@i)
SET @nn = RIGHT('0'+ convert(nvarchar,@i),2)
WHILE @i <= @PRM0 --- PROGRAM0 ループ
BEGIN
SET @iSql = @iSql + 'JSON_VALUE(jsonData,''$.PROGRAM[0].PARAMETER[' + @n + '].AREANAME'') as ANA0' + @nn + CHAR(10) + ','
SET @iSql = @iSql + 'JSON_VALUE(jsonData,''$.PROGRAM[0].PARAMETER[' + @n + '].METHOD'') as MTA0' + @nn + CHAR(10) + ','
SET @iSql = @iSql + 'JSON_VALUE(jsonData,''$.PROGRAM[0].PARAMETER[' + @n + '].RANGE.MIN'') as RGA0' + @nn + CHAR(10) + ','
SET @iSql = @iSql + 'JSON_VALUE(jsonData,''$.PROGRAM[0].PARAMETER[' + @n + '].RESULT'') as RTA0' + @nn + CHAR(10) + ','
SET @iSql = @iSql + 'JSON_VALUE(jsonData,''$.PROGRAM[0].PARAMETER[' + @n + '].RESULTVALUE'') as RVA0' + @nn + CHAR(10) + ','
SET @i = @i + 1
SET @n = convert(nvarchar,@i)
SET @nn = RIGHT('0'+ convert(nvarchar,@i),2)
END
END -- IF===== ▲ ======
-- PROGRAM[0] --
IF @PRM1 > 0
BEGIN -- IF===== ▼ ======
SET @i = 0
SET @n = convert(nvarchar,@i)
SET @nn = RIGHT('0'+ convert(nvarchar,@i),2)
WHILE @i <= @PRM1 --- PROGRAM1 ループ
BEGIN
SET @iSql = @iSql + 'JSON_VALUE(jsonData,''$.PROGRAM[1].PARAMETER[' + @n + '].AREANAME'') as ANA1' + @nn + CHAR(10) + ','
SET @iSql = @iSql + 'JSON_VALUE(jsonData,''$.PROGRAM[1].PARAMETER[' + @n + '].METHOD'') as MTA1' + @nn + CHAR(10) + ','
SET @iSql = @iSql + 'JSON_VALUE(jsonData,''$.PROGRAM[1].PARAMETER[' + @n + '].RANGE.MIN'') as RGA1' + @nn + CHAR(10) + ','
SET @iSql = @iSql + 'JSON_VALUE(jsonData,''$.PROGRAM[1].PARAMETER[' + @n + '].RESULT'') as RTA1' + @nn + CHAR(10) + ','
SET @iSql = @iSql + 'JSON_VALUE(jsonData,''$.PROGRAM[1].PARAMETER[' + @n + '].RESULTVALUE'') as RVA1' + @nn + CHAR(10) + ','
SET @i = @i + 1
SET @n = convert(nvarchar,@i)
SET @nn = RIGHT('0'+ convert(nvarchar,@i),2)
END
END -- IF===== ▲ ======
-- 終端の ',' 削除
SET @iSql = Left(@iSql,len(@iSql)-1)
-- print '1回目:' + @iSql
-- 後処理のiSql 追加
SET @pTbtCD = @TbtCD
SET @pOrder = @Order + '%'
SET @pSERIAL = @SERIAL --- + '%'
SET @pInsDT = @InsDT
SET @iSql = @iSql + ' FROM dWb_vceResult' -- order by InspectDT DESC'
SET @iSql = @iSql + ' where TbtCD = @pTbtCD and sOrder Like @pOrder and sSERIAL > @pSERIAL and InspectDT >= @pInsDT' -- + '%'
SET @iSql = @iSql + ' ORDER BY InspectDT DESC'
SET @iSql = replace(@iSql, 'TOP(5)','TOP(1000) TbtCD,sOrder,sSERIAL,InspectDT, ')
SET @ParamSQL = '@pTbtCD nvarchar(20),@pOrder nvarchar(12),@pSERIAL nvarchar(20),@pInsDT datetime'
print @iSql
--@iSql 実行
EXEC sp_executesql @iSql
,@ParamSQL
,@pTbtCD
,@pOrder
,@pSERIAL
,@pInsDT
END
GO
