这是我昨天提出的问题的后续:
您是否曾经遇到过 SQL Server 2008 返回与 SQL Server 2000 不同的结果集的情况?
我最初认为存储过程在 sql2000 和 sql2008 上给出了不同的结果,但我已经做了相当多的缩小问题范围的工作,并消除了相当多的代码以将其简化为一个简单/可重现的问题。总结是,一段 TSQL 当作为 proc 运行时返回不同的答案,与作为 TSQL 运行的相同代码位不同,但仅在我的客户端服务器上,不在我的任何一个测试服务器上。
当我运行这个 SQL 时:
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 美元:
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 美元,恰好是应有金额的两倍。
我真的很困惑。更奇怪的是,如果我备份这个数据库,然后将其复制到运行 sql2000 的 win2003 服务器或运行 SQL2008R2 的 win2008 服务器,它在两种情况下都能正常工作。所以seems这是导致问题的服务器或数据库设置,但已经没有什么可以检查的了 - 希望新的一双眼睛可以指出我明显遗漏的东西。