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

Question

This is the question for the question I asked yesterday:

Have you ever returned a different result from SQL Server 2008 than SQL Server 2000?

where I initially thought that the stored procedure yielded different results on sql2000 compared to sql2008, but I did quite a bit narrowing the problem and eliminated quite a bit of code to bring it to a simple / reproducible problem.The summary is that the TSQL part at startup how proc returns another answer that the same bit of code works like TSQL, but only on the server of my clients , and not on one 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 turned the above code into a stored procedure, for example:

  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 do this:

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

I get a debit amount of $ 248.54, which is twice as much as it should be.

I'm really at a standstill. The more complicated thing is that if I backup this database and then copy it to my win2003 server running sql2000 or to my win2008 server running SQL2008R2, it works correctly in both cases. Thus, it looks like this is a problem with the server or the database, but they did not have enough time to check - hoping that a fresh set of eyes could indicate that I did not see.

Answer 1

OK, here is my fix - this absolutely does not explain the original problem, but this is what I did:

Whenever I have a performance problem with a parameter, to solve this problem I declare "local" variables for all parameters, assign these parameters to these variables, and then use only local variables in the rest of proc, for example:

  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 the sniffing parameter is a problem, you can run the stored procedure through MGMT studio and get better performance than running it as SQL, and the changes (as above) usually fix.

In my case, I saw the difference between direct TSQL and the execution of proc (although this is not related to performance), I gave it a try - and it worked; I'm sorry that I did not have a better explanation, because, frankly, I would be scared to think that the SQL server would happen with unexpected results when using almost identical code.

I found this MS newsletter about a similar but different problem, which at least confirms that, under the right circumstances, the SQL server may give you bad answers, and this related error report using this keyword phrase:

Description: Two sessions, each of which many calls to procedure P with changing parameter values. procedure P executes a single request against static data, sometimes with the (RECOMPILE) option, and sometimes without it.

Sometimes P gives incorrect results (for playback below, this is usually about 1/2% - 1% of the time). When the results of P are incorrect, P returns either 0 or two times the expected number of rows.

Someone yesterday left a comment about sniffing options as an opportunity, but for some reason they deleted their comments (or response), so I can not give them credit for a hint.

Answer 2