ALTER PROCEDURE [dbo].[sp_QueryLIST_Json]
(
@compid VARCHAR(10),
@Opid VARCHAR(10),
@status INTEGER ,
@string VARCHAR(max)
)
AS
-- DECLARe
@string VARCHAR(8000)
-- SET
@string ='[ { "mark": "0", "CContractCode": "srq0040", "ContractUID": "{sssss}", "stgid": "0", "NotPumpPrice": "1.11111", "PumpPrice": "1.11111", "TowerCranePrice": "1.11111", "PriceDifference": "1.11111", "PriceETime": "2017-9-9", "CreateTime": "2017-9-9" }, { "mark": "0", "CContractCode": "srq0041", "ContractUID": "{sssss}", "stgid": "0", "NotPumpPrice": "1.11111", "PumpPrice": "1.11111", "TowerCranePrice": "1.11111", "PriceDifference": "1.11111", "PriceETime": "2017-9-9", "CreateTime": "2017-9-9" } ]'
--
IF
@status =1 --json 中有10 个字段
BEGIN
declare
@sint INTEGER -- 数据条数
SELECT
@sint = count(1) from parseJSON(@string) --sint fuzhi
declare
@CContractCode VARCHAR(50)
declare
@ContractUID VARCHAR(50)
declare
@stgid VARCHAR(50)
declare
@NotPumpPrice DECIMAL(18,6)
declare
@PumpPrice DECIMAL(18,6)
declare
@TowerCranePrice DECIMAL(18,6)
declare
@PriceDifference DECIMAL(18,6)
declare
@PriceETime VARCHAR(20)
declare
@CreateTime VARCHAR(20)
--
"mark":
"0",
--
"CContractCode":
"srq0040",
--
"ContractUID":
"{sssss}",
--
"stgid":
"0",
--
"NotPumpPrice":
"1.11111",
--
"PumpPrice":
"1.11111",
--
"TowerCranePrice":
"1.11111",
--
"PriceDifference":
"1.11111",
--
"PriceETime":
"2017-9-9",
--
"CreateTime":
"2017-9-9"
DECLARE
@n INTEGER
set
@n = 1
WHILE(
@n<=@sint/10)
BEGIN
DECLARE
@send INTEGER
DECLARE
@statr INTEGER
SELECT
@send = @n*10
SELECT
@statr =@send-9
-- SELECT
@statr statr
SELECT
@CContractCode=StringValue from (SELECT * FROM ( SELECT *, row_number() OVER( ORDER BY sequenceNo) k from parseJSON(@string)) as s WHERE k BETWEEN @statr and @send ) f where jname = 'CContractCode'
SELECT
@ContractUID=StringValue from (SELECT * FROM ( SELECT *, row_number() OVER( ORDER BY sequenceNo) k from parseJSON(@string)) as s WHERE k BETWEEN @statr and @send ) f where jname = 'ContractUID'
Select
@stgid=StringValue from (SELECT * FROM ( SELECT *, row_number() OVER( ORDER BY sequenceNo) k from parseJSON(@string)) as s WHERE k BETWEEN @statr and @send ) f where jname = 'stgid'
Select
@NotPumpPrice=StringValue from (SELECT * FROM ( SELECT *, row_number() OVER( ORDER BY sequenceNo) k from parseJSON(@string)) as s WHERE k BETWEEN @statr and @send ) f where jname = 'NotPumpPrice'
Select
@PumpPrice=StringValue from (SELECT * FROM ( SELECT *, row_number() OVER( ORDER BY sequenceNo) k from parseJSON(@string)) as s WHERE k BETWEEN @statr and @send ) f where jname = 'PumpPrice'
Select
@TowerCranePrice=StringValue from (SELECT * FROM ( SELECT *, row_number() OVER( ORDER BY sequenceNo) k from parseJSON(@string)) as s WHERE k BETWEEN @statr and @send ) f where jname = 'TowerCranePrice'
Select
@PriceDifference=StringValue from (SELECT * FROM ( SELECT *, row_number() OVER( ORDER BY sequenceNo) k from parseJSON(@string)) as s WHERE k BETWEEN @statr and @send ) f where jname = 'PriceDifference'
Select
@PriceETime=StringValue from (SELECT * FROM ( SELECT *, row_number() OVER( ORDER BY sequenceNo) k from parseJSON(@string)) as s WHERE k BETWEEN @statr and @send ) f where jname = 'PriceETime'
Select
@CreateTime=StringValue from (SELECT * FROM ( SELECT *, row_number() OVER( ORDER BY sequenceNo) k from parseJSON(@string)) as s WHERE k BETWEEN @statr and @send ) f where jname = 'CreateTime'
exec sp_insertUpDel_SM_ContractGradePriceDetail
0,
@compid,@CContractCode,@ContractUID,@Opid,@stgid,@NotPumpPrice,@PumpPrice,@TowerCranePrice,@PriceDifference,@PriceETime,@CreateTime,NULL
SELECT
@n=@n+1
END
END
