Clever Geek Handbook

Stored procedures return results when executed in SSMS, but only one is executed when code is executed

I have two stored procedures written in SQL Server 2012 and both executable in SQL Server Management Studio, which give their results as xml.

However, when these two procedures are imported into the Entity Data Model and then the functions to which they are mapped, only one is called, gives the same results as when executed from ssms.

I think it must have something to do with one of the input parameters, but assuming they are actually not that different. I don't understand why this is happening.

The two stored procedures are shown below. SPA works when executed from both environments, SPB starts when executed in ssms with date values ​​entered in options dialog like "2015-07-05", but fails when added as date value (derived from date down control), which creates a parameter like this # 5/7/2015 12:00:00 AM #.

Can anyone spot some obvious faux pas on my part in spb that would prevent it from executing when called from code, but still let ssms execute it correctly.

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

When executed from SSMS, SPB does the following:

<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>

When a function created in a model is executed from my application, it produces the following:

<PurchaseOrders/>

I would welcome any suggestions.

EDIT

Here is the code generated by Devart Entity Developer for 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

and it is called in my code like so:

 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 and End Dates are Date properties bound to the edit date of the controls.

For Spb we have:

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

And it's called like this:

 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

with start and end dates are type date properties.

+3


source to share


1 answer


The problem is most likely with the parameters, and in my experience, dates are always a mess.

The most common problem is month-day inversion, it happens in different scenarios, for example, implicit conversion between date and date. take a look at this:

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

the result is funny:

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

So you can try to debug your SPB, change the sentence WHERE

PATH ('Owner')

in SPB.
Temporarily memorize both conditions on @startDate/@endDate

and@VesselPln 

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


Then try calling it from your application, it should return all results.
Then re-add the condition to @VesselPln

, check it, I think it will throw something anyway.

If not, you've already found your problem, perhaps something related to multibyte strings nchar(10)

or less likely something related to ansi paddings and trailing spaces.

If so, add @startDate

also @endDate

to the SELECT

section PATH ('Owner')

to check how they are viewed by SPB:

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

So you should see in your xml file that the problem is.

Hope this helps.

0


source






More articles:


All Articles