sql - 在 SSMS 中执行时，存储过程都会返回结果，但从代码执行时只有一个会返回结果

Question

我有两个存储过程，都是用 SQL Server 2012 编写的，都在 SQL Server Management Studio 中执行，并以 xml 形式生成结果。

但是，当这两个过程导入到实体数据模型中，然后调用它们映射到的函数时，只有一个过程会产生与从 ssms 执行时相同的结果。

我认为这一定与其中一个输入参数有关，但考虑到它们并没有那么不同，我不知道为什么会发生这种情况。

这两个存储过程如下所示。 SPA 在从两个环境执行时都有效，SPB 在 ssms 中执行时运行，并在参数对话框中输入日期值，如“2015-07-05”，但在添加为日期值(从日期下拉列表中派生)时失败控制)，产生像这样的参数 #5/7/2015 12:00:00 AM#。

任何人都可以在 spb 中发现我的一些明显的错误，这些错误会阻止它在从代码调用时执行，但仍然允许 ssms 正确执行它。

SPA

Create PROCEDURE [dbo].[CreateErsSalesAddSubmissionXmlByDateRange]
    @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;

  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

SPB

CREATE PROCEDURE [dbo].[GetLandingsToBePurchasedByDateAndPln]
        @startDate DATE  ,
        @endDate DATE  ,
        @VesselPln nchar(10) 
AS
BEGIN
    --SET LANGUAGE 'British English';
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT 
        (SELECT
             ContactId AS '@ContactId',
             VesselOwner AS '@Owner',
             FORMAT(Owed, 'N2') AS '@Owed',
             FORMAT(WeeklyDeductionRate, 'C') AS '@WeeklyDeductionRate',
             FORMAT(FromMinimumReturn, 'C') AS '@FromMinimumReturn',
             FORMAT(DeductionRate, 'P') AS '@DeductionRate',
             FORMAT(TotalDeductions, 'N2') AS '@TotalDeductions',
             FORMAT(TempReturn, 'N2') AS '@TempReturn',
             FORMAT(InternalCommission, 'P') AS '@InternalCommissionRate',
             FORMAT(InternalDeduction, 'P') AS '@InternalDeductionRate',
             FORMAT(InternalCommissionAmount, 'N2') AS '@InternalCommissionAmount',
             FORMAT(InternalDeductionAmount, 'N2') AS '@InternalDeductionAmount',
             FORMAT(TotalToBeReturned, 'N2') AS '@TotalToBeReturned',
             (SELECT DISTINCT
                  ld1.ProductId AS '@ProductId',
                  FORMAT(AVG(ld1.UnitPrice), 'N2') AS '@Cost',
                  FORMAT(SUM(ld1.Quantity), 'N2') AS '@Quantity'
              FROM 
                  LandingDetails ld1
              INNER JOIN 
                  dbo.LandingHeaders lh1 ON ld1.LandingId = lh1.LandingId
              WHERE 
                  Posted = 0
                  AND lh1.VesselOwner = a.VesselOwner
              GROUP BY 
                  ld1.ProductId
              FOR XML PATH ('Products'), TYPE)
  FROM (SELECT
    Contacts.ContactId AS ContactId,
    LandingHeaders.VesselOwner AS VesselOwner,
    SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) AS Owed,
    SocietyMemberships.WeeklyDeductionRate AS WeeklyDeductionRate,
    SocietyMemberships.FromMinimumReturn AS FromMinimumReturn,
    Deductions.DeductionRate,
    Vessels.InternalCommission,
    Vessels.InternalDeduction,
    CASE
      WHEN SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) - (SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) * DeductionRate + WeeklyDeductionRate) > FromMinimumReturn THEN SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) * DeductionRate + WeeklyDeductionRate
      ELSE SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) * DeductionRate
    END AS TotalDeductions,


    --need to add some logic here I presume that utiles the sql below that is being used to calculate the total to be returned


    SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) -
    (CASE
      WHEN SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) - (SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) * DeductionRate + WeeklyDeductionRate) > FromMinimumReturn THEN SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) * DeductionRate + WeeklyDeductionRate
      ELSE SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) * DeductionRate
    END) AS TempReturn,



    (SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) -
    (CASE
      WHEN SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) - (SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) * DeductionRate + WeeklyDeductionRate) > FromMinimumReturn THEN SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) * DeductionRate + WeeklyDeductionRate
      ELSE SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) * DeductionRate
    END)) * InternalCommission AS InternalCommissionAmount,

    (SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) -
    (CASE
      WHEN SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) - (SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) * DeductionRate + WeeklyDeductionRate) > FromMinimumReturn THEN SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) * DeductionRate + WeeklyDeductionRate
      ELSE SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) * DeductionRate
    END)) * InternalDeduction AS InternalDeductionAmount,


    CASE
      WHEN (SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) -
        (CASE
          WHEN SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) - (SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) * DeductionRate + WeeklyDeductionRate) > FromMinimumReturn THEN SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) * DeductionRate + WeeklyDeductionRate
          ELSE SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) * DeductionRate
        END)) * InternalCommission > 0 THEN (SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) -
        (CASE
          WHEN SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) - (SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) * DeductionRate + WeeklyDeductionRate) > FromMinimumReturn THEN SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) * DeductionRate + WeeklyDeductionRate
          ELSE SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) * DeductionRate
        END)) + ((SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) -
        (CASE
          WHEN SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) - (SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) * DeductionRate + WeeklyDeductionRate) > FromMinimumReturn THEN SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) * DeductionRate + WeeklyDeductionRate
          ELSE SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) * DeductionRate
        END)) * InternalCommission)

      ELSE (SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) -
        (CASE
          WHEN SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) - (SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) * DeductionRate + WeeklyDeductionRate) > FromMinimumReturn THEN SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) * DeductionRate + WeeklyDeductionRate
          ELSE SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) * DeductionRate
        END)) + ((SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) -
        (CASE
          WHEN SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) - (SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) * DeductionRate + WeeklyDeductionRate) > FromMinimumReturn THEN SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) * DeductionRate + WeeklyDeductionRate
          ELSE SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) * DeductionRate
        END)) * InternalDeduction)
    END AS TotalToBeReturned





  FROM dbo.LandingDetails
  INNER JOIN dbo.LandingHeaders
    ON LandingDetails.LandingId = LandingHeaders.LandingId
  INNER JOIN dbo.Vessels
    ON LandingHeaders.VesselId = Vessels.VesselId
  INNER JOIN dbo.Contacts
    ON Vessels.OwnerId = Contacts.ContactId
  INNER JOIN dbo.SocietyMemberships
    ON Contacts.SocietyId = SocietyMemberships.SocietyId
  INNER JOIN dbo.Deductions
    ON Vessels.DeductionId = Deductions.DeductionId
  WHERE (LandingHeaders.Posted = 0)
  AND (LandingDate1 BETWEEN @startDate AND @endDate)
    AND LandingHeaders.VesselPLN = RTRIM(@VesselPln)
  GROUP BY ContactId,
           LandingHeaders.VesselOwner,
           SocietyMemberships.WeeklyDeductionRate,
           SocietyMemberships.FromMinimumReturn,
           Deductions.DeductionRate,
           Vessels.InternalCommission,
           Vessels.InternalDeduction) a
  ORDER BY ContactId

  FOR XML PATH ('Owner'), TYPE)

FOR XML PATH ('PurchaseOrders')
END

GO

从 SSMS 执行时，SPB 会生成以下内容:

<PurchaseOrders>
  <Owner ContactId="39" Owner="M Mouse" Owed="1,609.39" 
         WeeklyDeductionRate="$10.00" FromMinimumReturn="$110.00" 
         DeductionRate="1.50 %" TotalDeductions="34.14" 
         TempReturn="1,575.24" InternalCommissionRate="0.00 %" 
         InternalDeductionRate="0.00 %" InternalCommissionAmount="0.00" 
         InternalDeductionAmount="0.00" TotalToBeReturned="1,575.24">
    <Products ProductId="33" Cost="5.00" Quantity="0.40" />
    <Products ProductId="34" Cost="1.80" Quantity="0.90" />
    <Products ProductId="41" Cost="2.30" Quantity="1.30" />
    <Products ProductId="42" Cost="2.25" Quantity="1.30" />
    <Products ProductId="43" Cost="1.60" Quantity="10.50" />
    <Products ProductId="57" Cost="7.00" Quantity="13.30" />
    <Products ProductId="59" Cost="9.63" Quantity="47.00" />
    <Products ProductId="61" Cost="6.23" Quantity="32.60" />
    <Products ProductId="66" Cost="1.00" Quantity="5.60" />
    <Products ProductId="92" Cost="0.50" Quantity="4.80" />
    <Products ProductId="125" Cost="1.00" Quantity="3.80" />
    <Products ProductId="139" Cost="6.50" Quantity="3.90" />
    <Products ProductId="156" Cost="1.50" Quantity="1.70" />
    <Products ProductId="161" Cost="5.80" Quantity="44.20" />
    <Products ProductId="171" Cost="3.88" Quantity="12.00" />
    <Products ProductId="173" Cost="4.55" Quantity="32.50" />
    <Products ProductId="175" Cost="5.00" Quantity="52.90" />
    <Products ProductId="182" Cost="0.50" Quantity="18.50" />
    <Products ProductId="198" Cost="0.50" Quantity="27.40" />
    <Products ProductId="220" Cost="1.50" Quantity="38.60" />
    <Products ProductId="231" Cost="6.00" Quantity="0.90" />
    <Products ProductId="236" Cost="0.85" Quantity="2.10" />
  </Owner>
</PurchaseOrders>

当通过模型中创建的函数从我的应用程序执行时，它会生成以下内容；

<PurchaseOrders/>

欢迎提出任何建议。

编辑

这是由 Devart 的 SPA 实体开发人员创建的代码

Public Overridable Function CreateErsSalesAddSubmissionXmlByDateRange (ByVal uname As String, ByVal pword As String, ByVal sntype As String, ByVal action As String, ByVal salesContractRef As String, ByVal auctionId As String, ByVal startDate As Global.System.Nullable(Of System.DateTime), ByVal endDate As Global.System.Nullable(Of System.DateTime)) As ObjectResult(Of CreateErsSalesAddSubmissionXmlByDateRangeResult)
            Dim unameParameter As ObjectParameter
            If (Not uname Is Nothing) Then
                unameParameter = New ObjectParameter("uname", uname)
            Else
                unameParameter = New ObjectParameter("uname", GetType(String))
            End If
            Dim pwordParameter As ObjectParameter
            If (Not pword Is Nothing) Then
                pwordParameter = New ObjectParameter("pword", pword)
            Else
                pwordParameter = New ObjectParameter("pword", GetType(String))
            End If
            Dim sntypeParameter As ObjectParameter
            If (Not sntype Is Nothing) Then
                sntypeParameter = New ObjectParameter("sntype", sntype)
            Else
                sntypeParameter = New ObjectParameter("sntype", GetType(String))
            End If
            Dim actionParameter As ObjectParameter
            If (Not action Is Nothing) Then
                actionParameter = New ObjectParameter("action", action)
            Else
                actionParameter = New ObjectParameter("action", GetType(String))
            End If
            Dim salesContractRefParameter As ObjectParameter
            If (Not salesContractRef Is Nothing) Then
                salesContractRefParameter = New ObjectParameter("salesContractRef", salesContractRef)
            Else
                salesContractRefParameter = New ObjectParameter("salesContractRef", GetType(String))
            End If
            Dim auctionIdParameter As ObjectParameter
            If (Not auctionId Is Nothing) Then
                auctionIdParameter = New ObjectParameter("auctionId", auctionId)
            Else
                auctionIdParameter = New ObjectParameter("auctionId", GetType(String))
            End If
            Dim startDateParameter As ObjectParameter
            If (startDate.HasValue) Then
                startDateParameter = New ObjectParameter("startDate", startDate)
            Else
                startDateParameter = New ObjectParameter("startDate", GetType(Global.System.Nullable(Of System.DateTime)))
            End If
            Dim endDateParameter As ObjectParameter
            If (endDate.HasValue) Then
                endDateParameter = New ObjectParameter("endDate", endDate)
            Else
                endDateParameter = New ObjectParameter("endDate", GetType(Global.System.Nullable(Of System.DateTime)))
            End If
            Return DirectCast(Me, IObjectContextAdapter).ObjectContext.ExecuteFunction(Of CreateErsSalesAddSubmissionXmlByDateRangeResult)("FishTrackerProfessionalEntities.CreateErsSalesAddSubmissionXmlByDateRange", unameParameter, pwordParameter, sntypeParameter, actionParameter, salesContractRefParameter, auctionIdParameter, startDateParameter, endDateParameter)
        End Function

它在我的代码中被这样调用:

 Using ftpe = New FishTrackerProfessionalEntities
        Try
            Dim result = ftpe.CreateErsSalesAddSubmissionXmlByDateRange("uname", "pword", "B", "INSERT", String.Empty, String.Empty, ErsStartDate, ErsEndDate)
            xmlValue = String.Join(String.Empty, result.Select(Function(r) r.XMLF52E2B6118A111d1B10500805F49916B))
        Catch Ex As Exception

ErsStart 和 end 日期是绑定(bind)到日期编辑控件的 Date 类型的属性。

对于 Spb，我们有:

Public Overridable Function GetLandingsToBePurchasedByDateAndPln (ByVal startDate As Global.System.Nullable(Of System.DateTime), ByVal endDate As Global.System.Nullable(Of System.DateTime), ByVal VesselPln As String) As ObjectResult(Of CreateErsSalesAddSubmissionXmlByDateRangeResult)
        Dim startDateParameter As ObjectParameter
        If (startDate.HasValue) Then
            startDateParameter = New ObjectParameter("startDate", startDate)
        Else
            startDateParameter = New ObjectParameter("startDate", GetType(Global.System.Nullable(Of System.DateTime)))
        End If
        Dim endDateParameter As ObjectParameter
        If (endDate.HasValue) Then
            endDateParameter = New ObjectParameter("endDate", endDate)
        Else
            endDateParameter = New ObjectParameter("endDate", GetType(Global.System.Nullable(Of System.DateTime)))
        End If
        Dim VesselPlnParameter As ObjectParameter
        If (Not VesselPln Is Nothing) Then
            VesselPlnParameter = New ObjectParameter("VesselPln", VesselPln)
        Else
            VesselPlnParameter = New ObjectParameter("VesselPln", GetType(String))
        End If
        Return DirectCast(Me, IObjectContextAdapter).ObjectContext.ExecuteFunction(Of CreateErsSalesAddSubmissionXmlByDateRangeResult)("FishTrackerProfessionalEntities.GetLandingsToBePurchasedByDateAndPln", startDateParameter, endDateParameter, VesselPlnParameter)
    End Function

它的名字是这样的；

 Using ftpe = New FishTrackerProfessionalEntities
        Try
            Dim result = ftpe.GetLandingsToBePurchasedByDateAndPln(StartDate, EndDate, VesselPln)
            xmlValue = String.Join(String.Empty, result.Select(Function(r) r.XMLF52E2B6118A111d1B10500805F49916B))
        Catch Ex As Exception

开始日期和结束日期是日期类型的属性。

Answer 1

问题很可能是参数，根据我的经验，日期总是一团糟。

最常见的问题是月日反转，它发生在各种场景中，例如日期和日期时间之间的隐式转换。
看看这个:

declare @d1 date = '2016-02-03'
declare @d2 datetime = '2016-02-03'
select FORMAT(@d1, 'yyyy-MMMM-dd') ParDate, FORMAT(@d2, 'yyyy-MMMM-dd') ParDateTime

结果很有趣:

ParDate             ParDateTime
2016-febbraio-03    2016-marzo-02

所以你可以尝试调试你的SPB，修改SPB中PATH ('Owner')的WHERE子句。
暂时重置 @startDate/@endDate 和 @VesselPln

上的条件

-- AND (LandingDate1 BETWEEN @startDate AND @endDate)
-- AND LandingHeaders.VesselPLN = RTRIM(@VesselPln)

然后尝试从您的应用程序调用它，它应该返回所有结果。
然后在@VesselPln上重新添加条件，测试一下，我想它仍然会输出一些东西。

如果没有，您已经找到了问题，可能与多字节字符串 nchar(10) 相关，或者不太可能与 ansi 填充和尾随空格相关。

如果是，请将 @startDate 和 @endDate 添加到 PATH ('Owner') 的 SELECT 部分查看 SPB 如何看待它们:

FORMAT(@startDate, 'yyyy-MMMM-dd') as '@StartDate', 
FORMAT(@endDate, 'yyyy-MMMM-dd') as '@EndDate',

这样您应该可以在 xml 输出中看到问题所在。

我希望这会有所帮助。