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 INTEGERset @n = 1WHILE(@n<=@sint/10)BEGINDECLARE @send  INTEGERDECLARE @statr INTEGERSELECT @send = @n*10SELECT @statr =@send-9-- SELECT @statr statrSELECT  @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,NULLSELECT @n=@n+1END END