存储过程提供与 tsql 不同的结果集，仅在某些服务器上

这是我昨天问的一个问题的后续:

This is a followup to a question I asked yesterday:

您是否曾让 SQL Server 2008 返回与 SQL Server 2000 不同的结果集?

我最初认为存储过程在 sql2000 和 sql2008 上给出了不同的结果，但我已经对问题进行了更多的缩小并消除了相当多的代码以将其归结为一个简单/可重现的问题.总结是，当作为 proc 运行时，一段 TSQL 返回不同的答案，即与 TSQL 一样运行的相同代码位但仅在我的客户端服务器上，而不是在我的任一测试服务器上.

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.

当我运行这个 TSQL 时:

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

但是，当我将上述代码转换为这样的存储过程时，我得到了 124.27 美元的借记金额:

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

然后像这样执行它:

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

我的借方金额为 248.54 美元，正好是应有的两倍.

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

我真的被难住了.更奇怪的是，如果我备份这个数据库，然后将它复制到运行 sql2000 的 win2003 服务器或运行 SQL2008R2 的 win2008 服务器，它在这两种情况下都能正常工作.因此，似乎是服务器或数据库设置导致了问题，但已经没有什么可以检查的了 - 希望有新的眼光可以指出我遗漏的一些明显的东西.>

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.