CODEWITHSUNDEEP
sqlsql-serversql-server-2008stored-procedures
Peter Campbell
Peter Campbell

Reputation: 671

Result from Stored proc different to SSMS Query

I have the identical code in a stored proc and in a query window, but I get different results. The only difference is that the SP is selecting into a temp table and then selecting from the temp table and that it is running in an SP.

The SP has no parameters - so its not parameter sniffing, The SP has ANSI_NULLS ON.

RecId and invoiceOrderRecId are bigints, brand is nvarchar(10)

  SELECT 
      ZIOH.Brand, 
      Count(RecId) as NumDispatched
  FROM 
      ZFSINVOICEORDERHISTORY ZIOH
  WHERE 
      ZIOH.DISPATCHDATETIME  >=  CAST(CURRENT_TIMESTAMP AS DATE)
      AND 
      ZIOH.DISPATCHDATETIME < DATEADD(DD, 1, CAST(CURRENT_TIMESTAMP AS DATE))
      AND 
      ZIOH.INVOICEORDERRECID NOT IN 
                              (SELECT 
                                   RecId 
                               FROM
                                   ZFSINVOICEORDER ZIO
                               WHERE
                                   ZIO.PARTITION = ZIOH.PARTITION)
   GROUP BY ZIOH.Brand
   ORDER BY ZIOH.Brand

Results from Query Window

╔═══════╦═══════════════╗
║ Brand ║ NumDispatched ║
╠═══════╬═══════════════╣
║ CCO   ║             1 ║
║ CVDUK ║            13 ║
║ FLX   ║            12 ║
║ MSFR  ║            74 ║
║ MSGR  ║             1 ║
║ MSUK  ║           211 ║
║ PIAFR ║            25 ║
║ PIAUK ║           129 ║
╚═══════╩═══════════════╝

Result From SP

╔═══════╦═══════════════╗
║ Brand ║ NumDispatched ║
╠═══════╬═══════════════╣
║ CCO   ║             1 ║
║ CVDUK ║             7 ║
║ FLX   ║            12 ║
║ MSFR  ║            53 ║
║ MSUK  ║           147 ║
║ PIAFR ║            21 ║
║ PIAUK ║           121 ║
╚═══════╩═══════════════╝

Upvotes: 2

Views: 64

Answers (1)

Peter Campbell
Peter Campbell

Reputation: 671

An Uncommitted transaction was causing the difference in results - well that's a morning lost.

Upvotes: 2

Related Questions