Stored proc gives different result set than tsql, only on some servers

Question

This is a followup to a question I asked yesterday:

Have you ever had SQL Server 2008 return a different result set than SQL Server 2000?

where I originally thought that the stored procedure was giving differnt results on sql2000 versus sql2008, but I have done quite a bit more narrowing down of the problem and eliminated quite a bit of code to get it down to a simple/reproducible problem. The summary is, an piece of TSQL when run as a proc returns a different answer that the same bit of code running as as just TSQL, but only on my clients server, not on either of my test servers.

When I run this TSQL:

DECLARE @PropertyID int 
DECLARE @PortfolioID    int 
DECLARE @StartDate  datetime 
DECLARE @EndDate    datetime 
DECLARE @AcctMethod tinyint 

SET @PropertyId=3555
--SET @PortfolioId = null
SET @StartDate= '3/1/2010'
SET @EndDate='2/28/2011'
SET @AcctMethod=1

DECLARE    @ErrorMsg    varchar(70)
DECLARE @ExclAcct tinyint

SET NOCOUNT ON
CREATE TABLE #IncomeStatement (
    PropertyID      int,
    GLAccountID     int,
    SubTotalAccountID   int,
    Debits          money,
    Credits         money,
    YTDDebits       money,
    YTDCredits      money,
    PZDebits        money,
    PZCredits       money,
    AccountType     tinyint
)

--Initialize Temporary Table
INSERT INTO #IncomeStatement(PropertyID, GLAccountID, SubTotalAccountID, AccountType, Debits, Credits, YTDDebits, YTDCredits, PZDebits, PZCredits)
SELECT PropertyID, ID, SubTotalAccountID, AccountType, 0, 0, 0, 0, 0, 0
FROM ChartOfAccounts
WHERE (PropertyID = @PropertyID OR @PropertyID Is Null)
    AND (@PortfolioID is null OR PropertyID in (select PropertyID from PortfolioProperty where PortfolioID=@PortfolioID))
    AND (Category > 3 or CashFlowCode <> 0)

--Period Activity
IF @AcctMethod = 1
    SET @ExclAcct = 0
ELSE
    SET @ExclAcct = 1

UPDATE Bal
SET 
    Debits = Debits +  D.TotDebit,
    Credits = Credits +  D.TotCredit
FROM #IncomeStatement Bal
    INNER JOIN (SELECT GLAccountID, Sum(Debit) AS TotDebit, Sum(Credit) AS TotCredit
            FROM GLTransaction GT
            WHERE (GT.PropertyID = @PropertyID OR @PropertyID Is Null)
                AND AccountingMethod <> @ExclAcct 
                AND Posted = 1
                AND TranDate >= @StartDate
                AND TranDate <= @EndDate
            GROUP BY GLAccountID) AS D
        ON BAL.GLAccountID = D.GLAccountID 

select * from #IncomeStatement  where GLAccountID=11153 
drop table    #IncomeStatement

I get a debit amount of $124.27, however, when I turn the above code into a stored procedure like this:

   CREATE Procedure [dbo].[sp_test]
    @PropertyID int = Null,
    @PortfolioID    int = Null,
    @StartDate  datetime = Null,
    @EndDate    datetime = Null,
    @AcctMethod tinyint = 1

AS


DECLARE    @ErrorMsg    varchar(70)
DECLARE @ExclAcct tinyint

SET NOCOUNT ON
CREATE TABLE #IncomeStatement (
    PropertyID      int,
    GLAccountID     int,
    SubTotalAccountID   int,
    Debits          money,
    Credits         money,
    YTDDebits       money,
    YTDCredits      money,
    PZDebits        money,
    PZCredits       money,
    AccountType     tinyint
)

--Initialize Temporary Table
INSERT INTO #IncomeStatement(PropertyID, GLAccountID, SubTotalAccountID, AccountType, Debits, Credits, YTDDebits, YTDCredits, PZDebits, PZCredits)
SELECT PropertyID, ID, SubTotalAccountID, AccountType, 0, 0, 0, 0, 0, 0
FROM ChartOfAccounts
WHERE (PropertyID = @PropertyID OR @PropertyID Is Null)
    AND (@PortfolioID is null OR PropertyID in (select PropertyID from PortfolioProperty where PortfolioID=@PortfolioID))
    AND (Category > 3 or CashFlowCode <> 0)

--Period Activity
IF @AcctMethod = 1
    SET @ExclAcct = 0
ELSE
    SET @ExclAcct = 1

UPDATE Bal
SET 
    Debits = Debits +  D.TotDebit,
    Credits = Credits +  D.TotCredit
FROM #IncomeStatement Bal
    INNER JOIN (SELECT GLAccountID, Sum(Debit) AS TotDebit, Sum(Credit) AS TotCredit
            FROM GLTransaction GT
            WHERE (GT.PropertyID = @PropertyID OR @PropertyID Is Null)
                AND AccountingMethod <> @ExclAcct 
                AND Posted = 1
                AND TranDate >= @StartDate
                AND TranDate <= @EndDate
            GROUP BY GLAccountID) AS D
        ON BAL.GLAccountID = D.GLAccountID 

select * from #IncomeStatement  where GLAccountID=11153 
drop table    #IncomeStatement

and then execute it loke this:

EXEC sp_test @PropertyID=3555, @StartDate='03/01/2010', @EndDate='02/28/2011'

I get a debit amount of $248.54, which is exactly double what it should be.

I am really stumped. The odder thing, is that if I backup this database, and then copy it to either my win2003 server running sql2000, or my win2008 server running SQL2008R2, it works correctly in both cases. So, it seems it is a server or database setting that is causing the problem, but have run out of things to check - hoping a fresh set of eyes can point out something obvious I am missing.

Answer 1

OK, here is my fix - it absolutely doesn't not explain the original problem, but this is what I did:

Whenever I have a "parameter sniffing" performance issue, in order to solve that I declare 'local' variables for all parameters, assign those parameters to those variables, and then use only the local variables in the rest of the proc, like this:

  ALTER Procedure [dbo].[rptDateIncomeStatementPlusCash]
        @PropertyID int = Null,
        @PortfolioID    int = Null,
        @StartDate  datetime = Null,
        @EndDate    datetime = Null,
        @AcctMethod tinyint = 1
    AS
      DECLARE   @xPropertyID    int 
      DECLARE   @xPortfolioID   int 
      DECLARE   @xStartDate datetime 
      DECLARE   @xEndDate   datetime 
      DECLARE   @xAcctMethod    tinyint 

      SET @xPropertyID= @PropertyId
      SET @xPortfolioId = @PortfolioId
      SET @xStartDate = @StartDate
      SET @xEndDate = @EndDate
      SET @xAcctMethod = @AcctMethod

the similarity is that when parameter sniffing is an issue, you can run a stored proc thru MGMT studio and get better performance than running it as an SQL, and changes (like those above), usually fix it.

In my case I was seeing a difference between straight TSQL versus executing the proc(although it was not performance related), I gave it a try - and presto it worked; I wish I had a better explanation, because quite honestly I find it scary to think that SQL server will on occassion give inconsistent results when running nearly identical code.

I did find this bulletin from MS about a similar but different problem, that at least does confirm that under the right circumstances, SQL server may give you bad answers, and this related bug report with this key phrase:

Description: Two sessions each make many calls to a procedure P with changing parameter values. The procedure P executes one query against static data, sometimes with OPTION (RECOMPILE) and sometimes without.

Occasionally P gives incorrect results (for the repro below, this typically happens about 1/2% - 1% of the time). When P's results are wrong, P returns either 0 or twice the expected number of rows.

Someone yesterday left a comment about parameter sniffing as a possibility but for whatever reason they deleted their comments (or answer) so I can't give credit to them for the tip.