SqlServer常用函数及语法

SQL中启用事务,出错回滚

begin tran mytran --启用事务 
    begin try
        select * from rc_pdOrder --要执行的语句
        commit tran  --执行事务
    end try 
    begin catch 
        rollback tran --回滚事务 
    end catch

SQLserver中接处理JSON字符串生成表格,下面的代码复制执行就可以看到结果
看了好多别人写的,都是相互抄, 思路不清晰, 讲不明白原理, 自已写个实例,帮助有需要的人
这段JSON是后端请求数据,数据中有表头部分和表体部分,解晰后生成一个表格,是不是很方便呢

    --后端请求的JSON数据
    declare @ml nvarchar(max) = N'{"orderCode":"1000092","prdCode":"A0504772","prdName":"复合调味粉M06M06/15kg/袋","factory":"1100",
    "mls":[{"id":"1","item":4,"prdCodes":"Z0100390","rsvQty":"225.000","unit":"KG","qty":"225.000","qty1":null,"reservation":757,"rsItem":1,"stockQty":"","whName":"","batCode":"","wh":"","prdNames":"大豆分离蛋白HGK-A807哈高科","potCode":"0102","LAY_TABLE_INDEX":0},
    {"id":"2","item":5,"prdCodes":"Z0200567","rsvQty":"15.500","unit":"EA","qty":"15.500","qty1":null,"reservation":757,"rsItem":2,"stockQty":"","whName":"","batCode":"","wh":"","prdNames":"大号公司空白打印标签90×160mm","potCode":"0102","LAY_TABLE_INDEX":1},
    {"id":"3","item":6,"prdCodes":"Z0200960","rsvQty":"15.500","unit":"EA","qty":"15.500","qty1":null,"reservation":757,"rsItem":3,"stockQty":"","whName":"","batCode":"","wh":"","prdNames":"25kg印刷牛皮纸袋850×420×80mm","potCode":"0102","LAY_TABLE_INDEX":2}]}'
    SELECT orderCode,prdCode,prdName,item,potCode,prdCodes,prdNames,rsvQty,batCode,wh,qty,unit,ordersItem,reservation,rsItem
    FROM OPENJSON(@ml)  
    --这里是取JSON根节点下的键值,表头数据
      WITH (
        orderCode nvarchar(50) '$.orderCode',
        prdCode NVARCHAR(200) '$.prdCode',
        prdName NVARCHAR(200) '$.prdName',
        mls NVARCHAR(MAX) '$.mls' AS JSON --把根节点下的数组再转成SQL能识的单层JSON
      )
    OUTER APPLY OPENJSON(mls) --继续解析子级单层JSON,原理比较简单,清晰易懂,拿到的是表体数据
      WITH (item NVARCHAR(50) '$.id',  
      potCode NVARCHAR(50) '$.potCode',
      prdCodes nvarchar(50) '$.prdCodes',
      prdNames NVARCHAR(50) '$.prdNames',
      rsvQty decimal(18,3) '$.rsvQty',
      batCode NVARCHAR(50) '$.batCode',
      wh NVARCHAR(50) '$.wh',
      qty decimal(18,3) '$.qty',
      unit NVARCHAR(50) '$.unit',
      ordersItem NVARCHAR(50) '$.item',
      reservation NVARCHAR(50) '$.reservation',
      rsItem NVARCHAR(50) '$.rsItem');

效果图片