确保在只找到一个记录时不添加逗号，或者同时添加逗号。
确保在只找到一个记录时不添加逗号，或者同时添加逗号。
提问于 2016-01-25 10:39:11
考虑一下SQL查询的以下部分，该查询旨在以XML的形式构造复杂的返回：
ISNULL(Logbook1 + ',', '') + ISNULL(Logbook2 + ',', '') + ISNULL(Logbook3 + ',', '') AS '@logBookNums',本质上，这将搜索适当的记录。如果发现LogBook1、LogBook2或LogBook3不存在记录，它将返回一个空字符串。如果它在LogBook1中找到一条记录，它将返回该记录和一个尾随逗号。
不幸的是，向其发送XML的WCF服务被记录得令人震惊(实际上根本没有读取任何内容)，而且它正在拒绝一个带有尾随逗号的日志编号。
我的猜测是，要么以下列方式提交：
logBookNums="12345,,"或
logBookNums="12345"如何将上面的SQL行更改为输出其中一行或另一行，以及(如果查询找到两个日志号码)以产生如下输出：
logBookNums="12345,12346,"或
logBookNums="12345,12346"这是我需要插入一个解决方案来解决@logBookNum和@landingDecNum的困境的整个查询。
CREATE PROCEDURE dbo.CreateErsSalesAddSubmissionXmlByDateRange
-- Add the parameters for the stored procedure here
@uname VARCHAR(10) ,
@pword VARCHAR(10) ,
@sntype VARCHAR(1) ,
@action VARCHAR(10) ,
@salesContractRef VARCHAR(10),
@auctionId NCHAR(10) ,
@startDate DATE,
@endDate DATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT
RTRIM(@uname) AS '@uname',
RTRIM(@pword) AS '@pword',
(SELECT
@snType AS '@snType',
RTRIM(@action) AS '@action',
COALESCE(@salesContractRef, '') AS '@salesContractRef',
CONVERT(VARCHAR(10), DateOfPurchase, 112) AS '@saleDate',
RTRIM(COALESCE(@auctionID, '')) AS '@auctionID',
ISNULL(Logbook1 + ',', '') + ISNULL(Logbook2 + ',', '') + ISNULL(Logbook3 + ',', '') AS '@logBookNums',
ISNULL(LandingDecNumber1 + ',', '') + ISNULL(LandingDecNumber2 + ',', '') + ISNULL(LandingDecNumber3 + ',', '') AS '@landingDecNums',
COALESCE(VesselName, '') AS '@vesselName',
RTRIM(VesselPLN) AS '@vesselPln',
RTRIM(VesselOwner) AS '@vesselMasterOwner',
COALESCE(CONVERT(VARCHAR(10), LandingDate1, 112), '') AS '@landingDate1',
COALESCE(CONVERT(VARCHAR(10), LandingDate2, 112), '') AS '@landingDate2',
COALESCE(CONVERT(VARCHAR(10), LandingDate3, 112), '') AS '@landingDate3',
RTRIM(CountryOfLanding) AS '@countryOfLanding',
RTRIM(PortOfLanding) AS '@landingPortCode',
RTRIM(lh1.LandingId) AS '@internalRef',
(SELECT
COALESCE(RTRIM(SpeciesCode),'') AS '@speciesCode',
RTRIM(FishingArea) AS '@faoAreaCode',
COALESCE(RTRIM(IcesZone),'') AS '@ZoneCode',
COALESCE(RTRIM(ld.DisposalCode),'') AS '@disposalCode',
COALESCE(ld.FreshnessGrade,'') AS '@freshnessCode',
COALESCE(ld.ProductSize,'') AS '@sizeCode',
COALESCE(ld.PresentationCode,'') AS '@presentationCode',
COALESCE(ld.PresentationState,'') AS '@stateCode',
RTRIM(ld.NumberOfFish) AS '@numberOfFish',
FORMAT(ld.Quantity, 'N2') AS '@weightKgs',
FORMAT(Quantity * ld.UnitPrice, 'N2') AS '@value',
COALESCE(ld.Currency,'') AS '@currencyCode',
RTRIM(ld.WithdrawnDestinationCode) AS '@withdrawnDestinationCode',
RTRIM(ld.BuyersRegistrationCode) AS '@buyerReg',
RTRIM(ld.SalesContractRef) AS '@salesContractRef'
FROM LandingDetails ld
JOIN LandingHeaders lh
ON ld.LandingId = lh.LandingId
WHERE ld.LandingId = lh1.LandingId
FOR XML PATH ('salesline'), TYPE)
FROM LandingHeaders lh1
WHERE lh1.AllocatedErsId IS NULL AND lh1.LandingDate1 BETWEEN @startDate AND @endDate
ORDER BY VesselName,lh1.LandingId
FOR XML PATH ('salesnote'), TYPE)
FOR XML PATH ('ers')
END
GO编辑(下面是我尝试过的答案)
SELECT
RTRIM(@uname) AS '@uname'
,RTRIM(@pword) AS '@pword'
,(SELECT
@snType AS '@snType'
,RTRIM(@action) AS '@action'
,COALESCE(@salesContractRef, '') AS '@salesContractRef'
,CONVERT(VARCHAR(10), DateOfPurchase, 112) AS '@saleDate'
,RTRIM(COALESCE(@auctionID, '')) AS '@auctionID'
,(
SELECT ',' + CAST(LogbookX AS VARCHAR(100))
FROM
(
VALUES(Logbook1),(Logbook2),(Logbook3)
) AS x(LogbookX)
FOR XML PATH('')
),1,1,'')
AS NumList(Concatenated)
WHERE NumList.Concatenated IS NOT NULL AS '@logBookNums'
,ISNULL(LandingDecNumber1 + ',', '') + ISNULL(LandingDecNumber2 + ',', '') + ISNULL(LandingDecNumber3 + ',', '') AS '@landingDecNums'
,COALESCE(VesselName, '') AS '@vesselName'
,RTRIM(VesselPLN) AS '@vesselPln'
,RTRIM(VesselOwner) AS '@vesselMasterOwner'
,COALESCE(CONVERT(VARCHAR(10), LandingDate1, 112), '') AS '@landingDate1'
,COALESCE(CONVERT(VARCHAR(10), LandingDate2, 112), '') AS '@landingDate2'
,COALESCE(CONVERT(VARCHAR(10), LandingDate3, 112), '') AS '@landingDate3'
,RTRIM(CountryOfLanding) AS '@countryOfLanding'
,RTRIM(PortOfLanding) AS '@landingPortCode'
,RTRIM(lh1.LandingId) AS '@internalRef'附加编辑，以帮助澄清下面的答案中的注释
现在，修改后的全部查询如下：
CREATE PROCEDURE dbo.CreateErsSalesAddSubmissionXmlByDateRange
-- Add the parameters for the stored procedure here
@uname VARCHAR(10),
@pword VARCHAR(10),
@sntype VARCHAR(1),
@action VARCHAR(10),
@salesContractRef VARCHAR(10),
@auctionId NCHAR(10),
@startDate DATE,
@endDate DATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT
RTRIM(@uname) AS '@uname'
,RTRIM(@pword) AS '@pword'
,(SELECT
@snType AS '@snType'
,RTRIM(@action) AS '@action'
,COALESCE(@salesContractRef, '') AS '@salesContractRef'
,CONVERT(VARCHAR(10), DateOfPurchase, 112) AS '@saleDate'
,RTRIM(COALESCE(@auctionID, '')) AS '@auctionID'
,STUFF
((SELECT
',' + CAST(LogbookX AS VARCHAR(100))
FROM (
VALUES (Logbook1), (Logbook2), (Logbook3)
) AS x (LogbookX)
FOR XML PATH (''))
, 1, 1, '')
AS '@logBookNums'
,ISNULL(LandingDecNumber1 + ',', '') + ISNULL(LandingDecNumber2 + ',', '') + ISNULL(LandingDecNumber3 + ',', '') AS '@landingDecNums'
,COALESCE(VesselName, '') AS '@vesselName'
,RTRIM(VesselPLN) AS '@vesselPln'
,RTRIM(VesselOwner) AS '@vesselMasterOwner'
,COALESCE(CONVERT(VARCHAR(10), LandingDate1, 112), '') AS '@landingDate1'
,COALESCE(CONVERT(VARCHAR(10), LandingDate2, 112), '') AS '@landingDate2'
,COALESCE(CONVERT(VARCHAR(10), LandingDate3, 112), '') AS '@landingDate3'
,RTRIM(CountryOfLanding) AS '@countryOfLanding'
,RTRIM(PortOfLanding) AS '@landingPortCode'
,RTRIM(lh1.LandingId) AS '@internalRef'
,(SELECT
COALESCE(RTRIM(SpeciesCode), '') AS '@speciesCode'
,RTRIM(FishingArea) AS '@faoAreaCode'
,COALESCE(RTRIM(IcesZone), '') AS '@ZoneCode'
,COALESCE(RTRIM(ld.DisposalCode), '') AS '@disposalCode'
,COALESCE(ld.FreshnessGrade, '') AS '@freshnessCode'
,COALESCE(ld.ProductSize, '') AS '@sizeCode'
,COALESCE(ld.PresentationCode, '') AS '@presentationCode'
,COALESCE(ld.PresentationState, '') AS '@stateCode'
,RTRIM(ld.NumberOfFish) AS '@numberOfFish'
,FORMAT(ld.Quantity, 'N2') AS '@weightKgs'
,FORMAT(Quantity * ld.UnitPrice, 'N2') AS '@value'
,COALESCE(ld.Currency, '') AS '@currencyCode'
,RTRIM(ld.WithdrawnDestinationCode) AS '@withdrawnDestinationCode'
,RTRIM(ld.BuyersRegistrationCode) AS '@buyerReg'
,RTRIM(ld.SalesContractRef) AS '@salesContractRef'
FROM LandingDetails ld
JOIN LandingHeaders lh
ON ld.LandingId = lh.LandingId
WHERE ld.LandingId = lh1.LandingId
FOR XML PATH ('salesline'), TYPE)
FROM LandingHeaders lh1
WHERE lh1.AllocatedErsId IS NULL
AND lh1.LandingDate1 BETWEEN @startDate AND @endDate
ORDER BY VesselName, lh1.LandingId
FOR XML PATH ('salesnote'), TYPE)
FOR XML PATH ('ers')
END
GO当存在logBookNums时，我们将得到正确格式化的xml，如下所示。
然而，如果没有，我们应该得到logBookNums=“，但正如你所看到的，我们什么也得不到。
回答 2
Stack Overflow用户
回答已采纳
发布于 2016-01-25 11:13:09
你可以这样试试
你需要一个"IN"-search，没有像aNumber='123,345,678'那样的搜索
DECLARE @tbl TABLE(Logbook1 INT,Logbook2 INT,Logbook3 INT);
INSERT INTO @tbl VALUES
(12345,23456,56789)
,(234,NULL,NULL)
,(NULL,123,NULL)
,(NULL,NULL,NULL);
SELECT 'logbBookNums IN(' + NumList.Concatenated +')'
FROM @tbl
CROSS APPLY
(
SELECT STUFF
(
(
SELECT ',' + CAST(LogbookX AS VARCHAR(100))
FROM
(
VALUES(Logbook1),(Logbook2),(Logbook3)
) AS x(LogbookX)
FOR XML PATH('')
),1,1,'')
)AS NumList(Concatenated)
WHERE NumList.Concatenated IS NOT NULL结果
logbBookNums IN(12345,23456,56789)
logbBookNums IN(234)
logbBookNums IN(123)Stack Overflow用户
发布于 2016-01-25 11:13:53
好吧，你可以写它，以防当结构，它不是那么长：
CASE WHEN Logbook1 is not null
THEN
CASE WHEN Logbook2 is not null
THEN
CASE WHEN Logbook2 is not null
THEN Logbook1+','+Logbook2+','+Logbook3
ELSE Logbook1+','+Logbook2
ELSE
CASE WHEN Logbook3 is not null
THEN Logbook1+','+Logbook3
ELSE Logbook1
ELSE CASE WHEN ...页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/34990527
复制相关文章
相似问题
腾讯云开发者
