We have our main database on a server, there is this stored procedure; when we run it against the database, it returns wrong values.
But when I take a back up of this database and restore it on another server and run the exact same query, it returns the correct answer.
What can I do?
Is it possible that the configuration of SQL Server affects how a query returns results?
If yes where can I start looking for problem ?
Here is the stored procedure, the exact same procedure runs on both databases and both databases are identical.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[S_GheymatGozaryFIFOFroosh]
@AYear SMALLINT,
@LDate CHAR(8),
@OdCd VARCHAR(17),
@FromFirst BIT,
@SCd TINYINT
AS
DECLARE @LHId Int, @LHRadif SmallInt,
@LHFact_Date CHAR(8), @LHFact_No INT,
@LHStock_Cd TinyInt, @LQnt_Resid DECIMAL(18,4),
@LPrc_Resid DECIMAL(30,8)
DECLARE @LRId INT, @LRRadif SmallInt,
@LRFact_Date CHAR(8), @LRFact_No INT,
@LRStock_Cd TinyInt
DECLARE @Kind_Cd TINYINT, @StartDate CHAR(8)
DECLARE @Cnt INT
SET @Cnt = 0
IF @ldate IS NOT NULL AND @FromFirst = 1
BEGIN
DELETE FROM S_Fifo_Gheymat
WHERE (Acc_Year = @Ayear)
AND (@SCd = 0 OR H_Stock_Cd = @SCd)
AND (Od_Cd = @OdCd)
END
IF @SCd = 0
SET @Kind_Cd = 2
ELSE
SET @Kind_Cd = 1
SET @StartDate = Right(CAST(@AYear AS VARCHAR(4)), 2) + '/01/01'
SELECT
@LHId = H_Id,
@LHRadif = H_Radif,
@LHFact_Date = H_Fact_Date,
@LHFact_No = H_Fact_No,
@LHStock_Cd = H_Stock_Cd,
@LQnt_Resid = Qnt_Resid,
@LPrc_Resid = Prc_Resid,
@LRId = R_Id,
@LRRadif = R_Radif,
@LRFact_Date = R_Fact_Date,
@LRFact_No = R_Fact_No,
@LRStock_Cd = R_Stock_Cd
FROM
S_Fifo_Gheymat
WHERE
Acc_Year = @AYear
AND Od_Cd = @OdCd
AND (@SCd = 0 OR H_Stock_Cd = @SCd)
AND EXISTS (SELECT Id
FROM S_Dtl_Fct
WHERE Id = H_Id
AND Radif = H_Radif
AND Stock_Cd = H_Stock_Cd
AND Od_Cd = S_Fifo_Gheymat.Od_Cd)
AND EXISTS (SELECT Id
FROM S_Dtl_Fct
WHERE Id = R_Id
AND Radif = R_Radif
AND Stock_Cd = R_Stock_Cd
AND Od_Cd = S_Fifo_Gheymat.Od_Cd)
SELECT @LHId=ISNULL(@LHId,0),@LHRadif=IsNull(@LHRadif,0),@LHFact_Date=IsNull
(@LHFact_Date,@StartDate),@LHFact_No=IsNull(@LHFact_No,0),@LHStock_Cd=ISNULL
(@LHStock_Cd,0)
,@LQnt_Resid=ISNULL(@LQnt_Resid,0),@LPrc_Resid=ISNULL(@LPrc_Resid,0)
,@LRId=ISNULL(@LRId,0),@LRRadif=IsNull(@LRRadif,0),@LRFact_Date=IsNull
(@LRFact_Date,@StartDate),@LRFact_No=IsNull(@LRFact_No,0),@LRStock_Cd=ISNULL
(@LRStock_Cd,0)
---------------------------------------
IF @LDate IS NULL BEGIN
SELECT TOP 1 @LDate=Fact_Date
FROM S_Dtl_Fct D
LEFT OUTER JOIN S_Hed_Fct H ON D.Id=H.Id
LEFT OUTER JOIN dbo.S_STOCKS S ON D.Stock_Cd=S.Stock_Cd
LEFT OUTER JOIN U_Log U ON H.Id_Log=U.Id_Log AND U.Action_Cd=5
WHERE (H.Acc_Year=@AYear) AND (H.Flag=6) AND (D.Od_Cd=@OdCd) AND
(H.Tamam=0) AND (@SCd<>0 OR S.Estesna_Gp=0)
AND (
(H.Fact_Date>@LHFact_Date)
OR (H.Fact_Date=@LHFact_Date AND
H.Fact_No>@LHFact_No)
OR (H.Fact_Date=@LHFact_Date AND
H.Fact_No=@LHFact_No AND D.Radif>@LHRadif)
OR (H.Fact_Date=@LHFact_Date AND
H.Fact_No=@LHFact_No AND D.Radif=@LHRadif AND D.Stock_Cd>@LHStock_Cd)
)
AND (@SCd=0 OR D.Stock_Cd=@SCd) AND (H.VAZEIAT<>2) AND
(U.Id_Log IS NOT NULL)
ORDER BY H.Fact_Date
End
DECLARE @H TABLE ( H_Id INT,H_Radif SMALLINT,H_Fact_Date CHAR
(8),H_Fact_No INT,H_Stock_Cd TINYINT,Quantity Decimal(18,4),Un_Prc
MONEY,HTamam Bit
,R_Id INT,R_Radif SMALLINT,R_Fact_Date
CHAR(8),R_Fact_No INT,R_Stock_Cd TINYINT,Qnt_Resid Decimal(18,2),Prc_Resid
Decimal(30,8))
INSERT INTO @H
(H_Id,H_Radif,H_Fact_Date,H_Fact_No,H_Stock_Cd,Quantity,HTamam)
SELECT D.Id,D.Radif,H.Fact_Date,H.Fact_No,D.Stock_Cd,D.Quantity,H.Tamam
FROM S_Dtl_Fct D
LEFT OUTER JOIN S_Hed_Fct H ON D.Id=H.Id
LEFT OUTER JOIN dbo.S_STOCKS S ON D.Stock_Cd=S.Stock_Cd
WHERE (H.Acc_Year=@AYear) AND (H.Flag=6) AND (D.Od_Cd=@OdCd) AND
(H.Fact_Date<=@LDate) AND (@SCd<>0 OR S.Estesna_Gp=0)
AND (
(H.Fact_Date>@LHFact_Date)
OR (H.Fact_Date=@LHFact_Date AND H.Fact_No>@LHFact_No)
OR (H.Fact_Date=@LHFact_Date AND H.Fact_No=@LHFact_No
AND D.Radif>@LHRadif)
OR (H.Fact_Date=@LHFact_Date AND H.Fact_No=@LHFact_No
AND D.Radif=@LHRadif AND D.Stock_Cd>@LHStock_Cd)
)
AND (@SCd=0 OR D.Stock_Cd=@SCd) AND (H.VAZEIAT<>2)
ORDER BY H.Fact_Date,H.Fact_No,D.Radif,D.Stock_Cd
Delete S_Related_RH FROM @H H LEFT OUTER JOIN S_Related_RH R ON
H.H_Id=R.H_Id AND H.H_Radif=R.H_Radif
------------------------------------------
DECLARE @HQnt DECIMAL(18,4),@HDate CHAR(8),@SumQ DECIMAL(18,4),@SumG
MONEY,@HQntWithPrc DECIMAL(18,4)
SET @SumG=@LQnt_Resid*@LPrc_Resid
SET @SumQ=@LQnt_Resid
--
DECLARE Cr CURSOR FOR SELECT Quantity,H_Fact_Date,H_Id,H_Radif FROM @H FOR
UPDATE OF Un_Prc
Open Cr
Fetch Next From Cr InTo @HQnt,@HDate,@LHId,@LHRadif
While (@@Fetch_Status=0) AND (@LRId IS NOT NULL)
Begin
IF @HQnt<=@LQnt_Resid BEGIN
SET @LQnt_Resid=@LQnt_Resid-@HQnt
UPDATE @H SET
Un_Prc=@SumG/@SumQ,R_Id=@LRId,R_Radif=@LRRadif,R_Fact_Date=@LRFact_Date,
R_Fact_No=@LRFact_No,R_Stock_Cd=@LRStock_Cd
,Qnt_Resid=@LQnt_Resid,Prc_Resid=@LPrc_Resid
WHERE CURRENT OF Cr
IF @HQnt>0 BEGIN
INSERT INTO dbo.S_Related_RH
(H_Id,H_Radif,R_Id,R_Radif,Quantity)
VALUES (@LHId,@LHRadif,@LRId,@LRRadif,@HQnt)
END
SET @SumG=@LQnt_Resid*@LPrc_Resid
SET @SumQ=@LQnt_Resid
Fetch Next From Cr InTo @HQnt,@HDate,@LHId,@LHRadif
END ELSE BEGIN
IF @LQnt_Resid>0 BEGIN
INSERT INTO dbo.S_Related_RH
(H_Id,H_Radif,R_Id,R_Radif,Quantity)
VALUES (@LHId,@LHRadif,@LRId,@LRRadif,@LQnt_Resid)
END
SET @HQnt=@HQnt-@LQnt_Resid --????? ????????? ?????
SELECT TOP 1
@LRId=D.Id,@LRRadif=D.Radif,@LRFact_Date=H.Fact_Date,@LRFact_No=H.Fact_No,
@LRStock_Cd=D.Stock_Cd,@LQnt_Resid=D.QUANTITY
,@LPrc_Resid=CASE D.QUANTITY WHEN 0
THEN 0 ELSE ( (Un_Prc*D.QUANTITY)+ISNULL(Qnt_1,0) )/ D.QUANTITY END
FROM S_Dtl_Fct D
LEFT OUTER JOIN S_Hed_Fct H ON D.Id=H.Id
LEFT OUTER JOIN dbo.S_STOCKS S ON D.Stock_Cd=S.Stock_Cd
WHERE (H.Acc_Year=@AYear) AND (H.Flag=5) AND (D.Od_Cd=@OdCd)
AND (H.Fact_Date<=@HDate) AND (H.Tamam=1) AND (@SCd<>0 OR S.Estesna_Gp=0)
AND (
(H.Fact_Date>@LRFact_Date)
OR (H.Fact_Date=@LRFact_Date AND
H.Fact_No>@LRFact_No)
OR (H.Fact_Date=@LRFact_Date AND
H.Fact_No=@LRFact_No AND D.Radif>@LRRadif)
OR (H.Fact_Date=@LRFact_Date AND
H.Fact_No=@LRFact_No AND D.Radif=@LRRadif AND D.Stock_Cd>@LRStock_Cd)
)
AND (@SCd=0 OR D.Stock_Cd=@SCd) AND (H.VAZEIAT<>2)
ORDER BY H.Fact_Date,H.Fact_No,D.Radif,D.Stock_Cd
--
IF @LRId IS NOT NULL BEGIN
IF @HQnt<=@LQnt_Resid SET @HQntWithPrc=@HQnt ELSE SET
@HQntWithPrc=@LQnt_Resid
SET @SumG=@SumG+(@HQntWithPrc*@LPrc_Resid)
SET @SumQ=@SumQ+@HQntWithPrc
End
IF ISNULL(@LQnt_Resid,0)=0 Break
End
END
Close Cr
Deallocate Cr
DECLARE @E Int
SET @E=0
BEGIN TRAN
UPDATE D SET Un_Prc=G.Un_Prc
FROM S_Dtl_Fct D
INNER JOIN @H G ON D.Id=G.H_Id AND D.Radif=G.H_Radif
WHERE (G.HTamam=0) And (G.R_Id IS NOT NULL)
SET @Cnt=@@ROWCOUNT
Set @E=@E+@@Error
DELETE F FROM S_Fifo_Gheymat F
WHERE (Acc_Year=@Ayear) AND (@SCd=0 OR H_Stock_Cd=@SCd) AND
(Od_Cd=@OdCd)
And EXISTS (SELECT TOP 1 Od_Cd
FROM @H
WHERE (H_Stock_Cd=F.H_Stock_Cd) AND
(Od_Cd=@OdCd) AND (R_Id IS NOT NULL)
ORDER BY H_Fact_Date DESC ,H_Fact_No
DESC ,H_Radif DESC ,H_Stock_Cd DESC)
Set @E=@E+@@Error
INSERT INTO S_Fifo_Gheymat
(Acc_Year,H_Stock_Cd,OD_CD,R_Stock_Cd,H_Id,H_Fact_Date,H_Fact_No,
H_Radif,R_Id,R_Fact_Date,R_Fact_No,R_Radif,Qnt_Resid,Prc_Resid)
SELECT TOP 1
@AYear,H_Stock_Cd,@OdCd,R_Stock_Cd,H_Id,H_Fact_Date,H_Fact_No,H_Radif,
R_Id,R_Fact_Date,R_Fact_No,R_Radif,Qnt_Resid,Prc_Resid
FROM @H
WHERE R_Id IS NOT Null
ORDER BY H_Fact_Date DESC ,H_Fact_No DESC ,H_Radif DESC ,H_Stock_Cd Desc
Set @E=@E+@@Error
IF @E=0 COMMIT TRAN ELSE ROLLBACK TRAN
SELECT @Cnt Cnt,@LHFact_No LHFactNo,@LHFact_Date LHFactDate,@LHStock_Cd
LHStock_Cd,@LRFact_No LRFactNo,@LRFact_Date LRFactDate,@LRStock_Cd
LRStock_Cd
