sql server 小技巧： 动态拼接where+json处理
declare @field1 varchar(50)='1',
@field2 varchar(50)='2',
@field3 varchar(50)='3',
@field4 varchar(50)='4',
@field5 varchar(50)='5'
--低版本写法
declare @WHERE as varchar(2000) = STUFF((SELECT ' AND ' + C.V FROM (
select CASE WHEN ISNULL(@field1,'')<> '' then 1 else 0 end H, 'JWSN.Fcustomer = "'+@field1+'"' V UNION ALL
select CASE WHEN ISNULL(@field5,'')<> '' then 1 else 0 end, 'JWSN.FINSIDELOTNO = "'+@field5+'"' UNION ALL
select CASE WHEN ISNULL(@field2,'')<> '' then 1 else 0 end, 'JWSN.FJWSN like "'+@field2+'%"' UNION ALL
select CASE WHEN ISNULL(@field3,'')<> '' then 1 else 0 end, 'JWSN.FIMEI1 like "'+@field3+'%"' UNION ALL
select CASE WHEN ISNULL(@field4,'')<> '' then 1 else 0 end, 'JWSN.Fstate = "'+@field4+'"' UNION ALL
select 1 , 'ISNULL(LD1.FCUSTOMERID,0) = 60' UNION ALL select 1 , N'ISNULL(LD2.FCUSTOMERID,0) <> 60'
) C where C.H = 1 FOR XML PATH('')
), 1, 5, '')
select REPLACE(REPLACE( replace(@WHERE,'"',''''),'<','<'),'>','>')
--高版本写法 SQL Server 2016 及更早版本
SELECT @WHERE = STRING_AGG(C.V, ' AND ') -- 核心：按顺序拼接，分隔符为' AND '
FROM ( -- 原有子查询结构不变，保留HasVal判断和Condition生成逻辑
select CASE WHEN ISNULL(@field1,'')<> '' then 1 else 0 end H, 'JWSN.Fcustomer = "'+@field1+'"' V UNION ALL
select CASE WHEN ISNULL(@field5,'')<> '' then 1 else 0 end, 'JWSN.FINSIDELOTNO = "'+@field5+'"' UNION ALL
select CASE WHEN ISNULL(@field2,'')<> '' then 1 else 0 end, 'JWSN.FJWSN like "'+@field2+'%"' UNION ALL
select CASE WHEN ISNULL(@field3,'')<> '' then 1 else 0 end, 'JWSN.FIMEI1 like "'+@field3+'%"' UNION ALL
select CASE WHEN ISNULL(@field4,'')<> '' then 1 else 0 end, 'JWSN.Fstate = "'+@field4+'"' UNION ALL
select 1 , 'ISNULL(LD1.FCUSTOMERID,0) = 60' UNION ALL select 1 , N'ISNULL(LD2.FCUSTOMERID,0) <> 60'
) C where C.H = 1 -- 仅保留有值的Condition
select REPLACE(REPLACE( replace(@WHERE,'"',''''),'<','<'),'>','>')
--然后你就可以快乐拼接SQl并在存储过程中执行了
declare @PrmDef nvarchar(100) = N'@field1 varchar(50),@field2 varchar(50),@field3 varchar(50),@field4 varchar(50)'
declare @SQL nvarchar(max) = N'select '''+REPLACE(@WHERE,'''','''''')+''' as [WHERE], @field1 [@field1], @field2[@field2],@field3[@field3],@field4[@field4]'
EXECUTE sp_executesql @SQL, @PrmDef,@field1= @field1,@field2 =@field2,@field3 =@field3,@field4=@field4
--如果你想输出也可以
set @PrmDef = N'@field1 varchar(50),@field2 varchar(50),@field3 varchar(50),@field4 varchar(50) OUT'
set @SQL = N'select '''+REPLACE(@WHERE,'''','''''')+''' as [WHERE], @field1 [@field1], @field2[@field2],@field3[@field3],@field4[@field4]; set @field4 = ''999'''
EXECUTE sp_executesql @SQL, @PrmDef,@field1,@field2,@field3,@field4 out
--EXECUTE sp_executesql @SQL, @PrmDef,@field1 = @field1,@field2 = @field2,@field3 = @field3,@field4 = @field4 OUTPUT; -- 你也可以用这种
select @field4
输出
JWSN.Fcustomer = '1' AND JWSN.FINSIDELOTNO = '5' AND JWSN.FJWSN like '2%' AND JWSN.FIMEI1 like '3%' AND JWSN.Fstate = '4' AND ISNULL(LD1.FCUSTOMERID,0) = 60 AND ISNULL(LD2.FCUSTOMERID,0) <> 60
| WHERE | @field1 | @field2 | @field3 | @field4 |
|---|---|---|---|---|
JWSN.Fcustomer = '1' AND JWSN.FINSIDELOTNO = '5' AND JWSN.FJWSN like '2%' AND JWSN.FIMEI1 like '3%' AND JWSN.Fstate = '4' AND ISNULL(LD1.FCUSTOMERID,0) = 60 AND ISNULL(LD2.FCUSTOMERID,0) <> 60 |
1 | 2 | 3 | 4 |
--JSON转表
DECLARE @FMONTHS TABLE (FKEY NVARCHAR(50),FVALUE NVARCHAR(50))
INSERT INTO @FMONTHS (FKEY,FVALUE)
SELECT FKEY,FVALUE FROM OPENJSON('[
{"FKEY":"10","FVALUE":"A"},
{"FKEY":"11","FVALUE":"B"},
{"FKEY":"12","FVALUE":"C"}]
')WITH(FKEY NVARCHAR(50),FVALUE NVARCHAR(50))
select * from @FMONTHS
| FKEY | FVALUE |
|---|---|
| 10 | A |
| 11 | B |
| 12 | C |
表转JSON
自动生成JSON数组，键名与列名一致
declare @JSON varchar(max) = (
SELECT top(10) FINSIDELOTNO, FHWMODEL,FMODELNO,FPROJECTID,FCUSTOMERID,
FBOMCODE,FPLATE,fline,FQTQNTY,FBEGINSN,FENDSN
FROM tb_PP_Lotmain
FOR JSON AUTO
)
select @JSON
go
输出
[{
"FINSIDELOTNO": "03337794-SPQ-1",
"FHWMODEL": "P50",
"FMODELNO": "P50",
"FPROJECTID": "TD02220001280",
"FCUSTOMERID": "41",
"FBOMCODE": "TD02220001280",
"FPLATE": "0009",
"fline": "06",
"FBEGINSN": "",
"FENDSN": ""
},
...
{
"FINSIDELOTNO": "1000000921-1",
"FHWMODEL": "EP560",
"FMODELNO": "EP560",
"FPROJECTID": "TD51080075630",
"FCUSTOMERID": "41",
"FBOMCODE": "TD51080075630",
"FPLATE": "0009",
"fline": "01",
"FBEGINSN": "",
"FENDSN": ""
}]
自定义键名 + 嵌套结构
declare @JSON varchar(max) = (
SELECT top(10) FINSIDELOTNO 'Order.ID', FHWMODEL 'Order.HWMODEL',FMODELNO 'Order.MODELNO',
FPROJECTID 'Order.PROJECTID',FCUSTOMERID 'Order.CUSTOMERID',
FBOMCODE 'Order.BOMCODE',FPLATE 'Order.PLATE',fline 'Order.LINE',FQTQNTY,FBEGINSN,FENDSN
FROM tb_PP_Lotmain
FOR JSON PATH, ROOT('WorkOrders')
)
select @JSON
go
输出
{
"WorkOrders": [{
"Order": {
"ID": "03337794-SPQ-1",
"HWMODEL": "P50",
"MODELNO": "P50",
"PROJECTID": "TD02220001280",
"CUSTOMERID": "41",
"BOMCODE": "TD02220001280",
"PLATE": "0009",
"LINE": "06"
},
"FBEGINSN": "",
"FENDSN": ""
}, {
"Order": {
"ID": "0508",
"HWMODEL": "EP720",
"MODELNO": "EP720DSDA",
"PROJECTID": "TD51080038730",
"CUSTOMERID": "41",
"BOMCODE": "TD51080038730",
"PLATE": "0009"
},
"FBEGINSN": "",
"FENDSN": ""
}]
}
带根节点的JSON
declare @JSON varchar(max) = (
SELECT top(10) FINSIDELOTNO, FHWMODEL,FMODELNO,FPROJECTID,FCUSTOMERID,
FBOMCODE,FPLATE,fline,FQTQNTY,FBEGINSN,FENDSN
FROM tb_PP_Lotmain
FOR JSON PATH, ROOT('WorkOrders')
)
select @JSON
go
输出
{
"WorkOrders": [{
"FINSIDELOTNO": "03337794-SPQ-1",
"FHWMODEL": "P50",
"FMODELNO": "P50",
"FPROJECTID": "TD02220001280",
"FCUSTOMERID": "41",
"FBOMCODE": "TD02220001280",
"FPLATE": "0009",
"fline": "06",
"FBEGINSN": "",
"FENDSN": ""
}, {
"FINSIDELOTNO": "0508",
"FHWMODEL": "EP720",
"FMODELNO": "EP720DSDA",
"FPROJECTID": "TD51080038730",
"FCUSTOMERID": "41",
"FBOMCODE": "TD51080038730",
"FPLATE": "0009",
"FBEGINSN": "",
"FENDSN": ""
}]
}
带跟节点的JSON 嵌套
declare @JSON varchar(max) = (
SELECT top(10) FINSIDELOTNO, FHWMODEL,FMODELNO,FPROJECTID,FCUSTOMERID,
FBOMCODE,FPLATE,fline,FQTQNTY,FBEGINSN,FENDSN,
(
select LD.FSN from dbo.tb_PP_Lotdetial LD
where LD.FINSIDELOTNO = A.FINSIDELOTNO
FOR JSON PATH
) as 'Barcodes'
FROM tb_PP_Lotmain A
FOR JSON PATH, ROOT('WorkOrders')
)
select @JSON
go
输出
{
"WorkOrders": [{
"FINSIDELOTNO": "4503350704-2",
"FHWMODEL": "Mate70",
"FMODELNO": "Mate70",
"FPROJECTID": "TD51080077800",
"FCUSTOMERID": "41",
"FBOMCODE": "TD51080077800",
"FPLATE": "0009",
"fline": "02",
"FBEGINSN": "",
"FENDSN": "",
"Barcodes": [{
"FSN": "861460084988660"
}]
}, {
"FINSIDELOTNO": "PSACDDP2501362",
"FHWMODEL": "Mate60Pro",
"FMODELNO": "Mate60Pro",
"FPROJECTID": "TD51080070580",
"FCUSTOMERID": "41",
"FBOMCODE": "TD51080070580",
"FPLATE": "0009",
"fline": "02",
"FBEGINSN": "",
"FENDSN": "",
"Barcodes": [{
"FSN": "866863071141802"
}, {
"FSN": "866863071149532"
}]
}]
}
带跟节点的JSON 嵌套
declare @JSON varchar(max) = (
SELECT top(10) FINSIDELOTNO, FHWMODEL,FMODELNO,FPROJECTID,FCUSTOMERID,
FBOMCODE,FPLATE,fline,FQTQNTY,FBEGINSN,FENDSN,
(
select LD.FSN from dbo.tb_PP_Lotdetial LD
where LD.FINSIDELOTNO = A.FINSIDELOTNO
FOR JSON PATH
) as 'H.Barcodes'
FROM tb_PP_Lotmain A
FOR JSON PATH, ROOT('WorkOrders')
)
select @JSON
go
输出
{
"WorkOrders": [{
"FINSIDELOTNO": "4503350704-2",
"FHWMODEL": "Mate70",
"FMODELNO": "Mate70",
"FPROJECTID": "TD51080077800",
"FCUSTOMERID": "41",
"FBOMCODE": "TD51080077800",
"FPLATE": "0009",
"fline": "02",
"FBEGINSN": "",
"FENDSN": "",
"H": {
"Barcodes": [{
"FSN": "861460084988660"
}]
}
}, {
"FINSIDELOTNO": "PSACDDP2501362",
"FHWMODEL": "Mate60Pro",
"FMODELNO": "Mate60Pro",
"FPROJECTID": "TD51080070580",
"FCUSTOMERID": "41",
"FBOMCODE": "TD51080070580",
"FPLATE": "0009",
"fline": "02",
"FBEGINSN": "",
"FENDSN": "",
"H": {
"Barcodes": [{
"FSN": "868189071051470"
}, {
"FSN": "868586072420705"
}, {
"FSN": "869339078059518"
}]
}
}, {
"FINSIDELOTNO": "4503350664-4",
"FHWMODEL": "Mate70Pro",
"FMODELNO": "Mate70Pro",
"FPROJECTID": "TD51080079100",
"FCUSTOMERID": "41",
"FBOMCODE": "TD51080079100",
"FPLATE": "0009",
"fline": "02",
"FBEGINSN": "",
"FENDSN": "",
"H": {
"Barcodes": [{
"FSN": "869147070715957"
}, {
"FSN": "869147070716864"
}, {
"FSN": "869147070716906"
}]
}
}]
}
解析JSON字符串
declare @CommandArgs varchar(max) = N'{
"ID_LdConfirm": null,
"ComfirmUser": "ComfirmUser",
"RoleName": "RoleName",
"FGZPC": "FGZPC",
"TaskOrderInfo": {
"FInsideLotNo": "FInsideLotNo",
"FactoryCode": "FactoryCode",
"ModelName": "ModelName",
"FMatcode": "FMatcode",
"RAN": "RAN",
"LineBody": "LineBody"
},
"FirstLaserSnInfo": {
"Barcode": "FirstLaserSnInfo",
"ClearCode": "ClearCode1",
"BarcodeLevel": "BarcodeLevel",
"BarcodeSize": "BarcodeSize"
},
"LastLaserSnInfo": {
"Barcode": "LastLaserSnInfo",
"ClearCode": "ClearCode2",
"CurrentQuality": "165",
"TotalQuality": "144"
},
"LaserParameters": [
{
"SideName": "SideNameA",
"FrequencySet": "FrequencySet1",
"PowerSet": "PowerSet1",
"SpeedSet": "SpeedSet1"
},
{
"SideName": "SideNameB",
"FrequencySet": "FrequencySet2",
"PowerSet": "PowerSet2",
"SpeedSet": "SpeedSet2"
}
]
}';
select * from openjson(JSON_QUERY(@CommandArgs, '$.TaskOrderInfo'))
select * from openjson(@CommandArgs)
SET @CommandArgs = JSON_MODIFY(@CommandArgs, '$.ComfirmUser', '王'); --修改
select ISJSON(@CommandArgs) ISJSON,
JSON_VALUE(@CommandArgs,'$.ID_LdConfirm') ID_LdConfirm,
JSON_VALUE(@CommandArgs,'$.ComfirmUser') ComfirmUser,
JSON_VALUE(@CommandArgs,'$.RoleName') RoleName,
JSON_VALUE(@CommandArgs,'$.FGZPC') FGZPC,
JSON_VALUE(@CommandArgs,'$.TaskOrderInfo.FInsideLotNo') FInsideLotNo
输出json对象
declare @VENDER varchar(50) = 'VENDER',@CPN varchar(50) = 'CPN',
@LINECODE varchar(50) = 'LINECODE',@DATE_FORAMT varchar(50) = 'DATE_FORAMT',
@AUTO_NUM varchar(50) = 'AUTO_NUM'
SELECT
@VENDER as VENDER,
@CPN as EQUNO,
@LINECODE as LINECODE,
@DATE_FORAMT AS DATECODE,
@AUTO_NUM AS AUTO_NUM
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
输出
{
"VENDER": "VENDER",
"EQUNO": "CPN",
"LINECODE": "LINECODE",
"DATECODE": "DATE_FORAMT",
"AUTO_NUM": "AUTO_NUM"
}
转载保留源出处即可，商业使用请自行鉴别，使用本博客中公开内容做任何违法犯罪于本作者无关
浙公网安备 33010602011771号