sql server 存储过程 中处理json

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
展开阅读全文

没有更多推荐了,返回首页