I have a stored procedure that is acting kinda funky. I didn't write it, and so that could be contributing to my not understanding it, but I think something is fouled up and I'd like to ask for your review because data I would have thought my delete statement removed is not being removed.
First look at the code. My problem is in the section of code that starts with a comment that I input on 4/13 towards the very end of the code. I'll include a problem description after this...
When I run this code, I get a really freaky result set with plenty of rows that are supposed to have been deleted still existing.
I use this to run:
The messages window shows this...
This tells me that the code enters into my condition, that the value is 40 and that the next condition isn't being run. HOWEVER, there are plenty of rows with data that should have called for the row to be deleted based on the value and parameter in the TRANAMT field!!!
Any ideas would be appreciated!!!
Thanks,
Keith
First look at the code. My problem is in the section of code that starts with a comment that I input on 4/13 towards the very end of the code. I'll include a problem description after this...
Code:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[uspCashierPaymentBalancing5_test]
-- Add the parameters for the stored procedure here
--@NAME varchar(50),
--@EXTERNAL_CHARGE VARCHAR(8),
@STARTDATE DATETIME,
@ENDDATE DATETIME,
@TransAmount NUMERIC(15,2)
AS
CREATE TABLE #FINAL(
EMPLOYEE_NAME VARCHAR(50),
EMPLAST VARCHAR(30),
EMPID INT,
DRWRKEY INT,
CHARGE_TYPE VARCHAR(10),
CHARGE_ID VARCHAR (24),
NAMELAST VARCHAR(50),
CHARGE_TOTAL NUMERIC(15,2),
PAYMENT_METHOD VARCHAR(6),
CHECK_NUM VARCHAR(12),
CHECK_NAME VARCHAR(30),
TRANSACTION_NUM NUMERIC(9,0),
REFTRANNO NUMERIC(9,0),
TRANAMT NUMERIC(15,2),
TRANTYPE VARCHAR(10),
TRANDATE DATETIME,
COMMENTS VARCHAR(4000),
ACCTNO VARCHAR(24),
TRANPAYER VARCHAR(50),
DRWRTRANNO INT,
ADJTYPE VARCHAR(8))
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--DECLARE @ENDDATE DATETIME,@STARTDATE DATETIME
--SET @ENDDATE = '9/5/2006' SET @STARTDATE = '9/5/2006'
-- Insert statements for procedure here
SET @ENDDATE = @ENDDATE + 1
BEGIN
INSERT INTO #FINAL(EMPLOYEE_NAME,EMPLAST,EMPID,DRWRKEY,CHARGE_TYPE,CHARGE_ID,NAMELAST,CHARGE_TOTAL,PAYMENT_METHOD,CHECK_NUM,
CHECK_NAME,TRANSACTION_NUM,REFTRANNO,TRANAMT,TRANTYPE,TRANDATE,COMMENTS,ACCTNO,TRANPAYER,DRWRTRANNO,ADJTYPE)
SELECT DISTINCT (ISNULL(RS.EMPFIRST,'UNKNOWN') + ' ' + ISNULL(RS.EMPLAST,'UNKNOWN')) AS EMPLOYEE_NAME,RS.EMPLAST,RS.EMPID,
RS.DRWRKEY,
(CASE
WHEN RS.CHGTYPE IS NULL OR RS.CHGTYPE = ''
THEN 'UTILITIES'
ELSE RS.CHGTYPE
END) AS CHGTYPE,
RS.CHGID,RS.NAMELAST,RS.CHGTOT,RS.TENDER,RS.CHECKNO,RS.CHECKNAME,RS.REGTRANNO,RS.REFTRANNO,
(CASE
WHEN (RS.DISTAMT IS NULL AND RS.PAYAMT IS NOT NULL)
THEN RS.PAYAMT
WHEN (RS.DISTAMT IS NULL AND RS.PAYAMT IS NULL)
THEN RS.RTTRANAMT
ELSE RS.DISTAMT
END) AS TRANAMT,RS.TRANTYPE,RS.ADDDTTM,
(CASE
WHEN (RS.COMMENTS IS NULL AND RS.DTCOMMENTS IS NOT NULL)
THEN RS.DTCOMMENTS
WHEN (RS.COMMENTS IS NULL AND RS.DTCOMMENTS IS NULL)
THEN 'NO COMMENTS'
ELSE RS.COMMENTS
END) AS COMMENTS,
(CASE
WHEN (RS.ACCTNO IS NULL AND RS.ACCTNO2 IS NOT NULL)
THEN RS.ACCTNO2
WHEN (RS.ACCTNO IS NULL AND RS.ACCTNO2 IS NULL)
THEN 'UNKNOWN'
ELSE RS.ACCTNO
END) AS ACCTNO,
(CASE
WHEN (RS.TRANPAYER IS NULL AND RS.CHECKNAME IS NOT NULL)
THEN RS.CHECKNAME
WHEN (RS.TRANPAYER IS NULL AND RS.CHECKNAME IS NULL AND RS.CHGID IS NOT NULL)
THEN RS.CHGID
WHEN (RS.TRANPAYER IS NULL AND RS.CHECKNAME IS NULL AND RS.CHGID IS NULL AND RS.ACCTNO IS NOT NULL)
THEN 'ACCT: ' + RS.ACCTNO
ELSE RS.TRANPAYER
END) AS TRANPAYER,RS.DRWRTRANNO,ADJTYPE
FROM HVPRD..IMSV7.RPT_SQLCASHPAYBLN_V RS
WHERE RS.ADDDTTM > @STARTDATE AND RS.ADDDTTM < @ENDDATE
ORDER BY RS.ADDDTTM
UPDATE #FINAL
SET CHARGE_TYPE = ISNULL(B.CHGTYPE,'UTILITIES'),
CHARGE_ID = B.CHGID,
NAMELAST = B.NAMELAST,
CHECK_NUM = B.CHECKNO,
TRANPAYER = (CASE
WHEN (B.TRANPAYER IS NULL AND B.CHECKNAME IS NOT NULL)
THEN B.CHECKNAME
WHEN (B.TRANPAYER IS NULL AND B.CHECKNAME IS NULL AND B.CHGID IS NOT NULL)
THEN B.CHGID
WHEN (B.TRANPAYER IS NULL AND B.CHECKNAME IS NULL AND B.CHGID IS NULL AND B.ACCTNO IS NOT NULL)
THEN 'ACCT: ' + B.ACCTNO
ELSE B.TRANPAYER
END)
FROM #FINAL A, HVPRD..IMSV7.RPT_SQLCASHPAYBLN_V B
WHERE A.REFTRANNO = B.DRWRTRANNO
AND A.TRANTYPE IN ('VOID','ADJ')
--SELECT * FROM #FINAL WHERE TRANTYPE = 'ADJ'
UPDATE #FINAL
SET TRANAMT = (CASE
WHEN TRANAMT IS NULL --OR TRANAMT = ''
THEN CHARGE_TOTAL
ELSE TRANAMT
END),
TRANTYPE = (CASE
WHEN TRANAMT IS NULL --OR TRANAMT = ''
THEN 'ADJ'
ELSE TRANTYPE
END)
SELECT * INTO #UPDATE
FROM #FINAL
UPDATE #FINAL
SET CHARGE_TYPE = B.CHARGE_TYPE,
ACCTNO = B.ACCTNO
FROM #FINAL A
JOIN #UPDATE B
ON A.REFTRANNO = B.DRWRTRANNO
WHERE A.REFTRANNO <> 1
AND A.TRANTYPE IN ('VOID','ADJ')
UPDATE #FINAL
SET TRANAMT = (CASE
WHEN ADJTYPE LIKE '%/O'
THEN ABS(TRANAMT)
WHEN ADJTYPE LIKE '%/S'
THEN -TRANAMT
ELSE TRANAMT
END)
SELECT DISTINCT EMPLOYEE_NAME,EMPLAST,EMPID,DRWRKEY,CHARGE_TYPE,CHARGE_ID,NAMELAST,CHARGE_TOTAL,PAYMENT_METHOD,CHECK_NUM,
CHECK_NAME,TRANSACTION_NUM,REFTRANNO,TRANAMT,TRANTYPE,TRANDATE,COMMENTS,ACCTNO,TRANPAYER,DRWRTRANNO,ADJTYPE
FROM #FINAL
ORDER BY EMPLAST
--
-- 4/13/2007 Added by Keith for Rocky to allow casheirs to look for a specific dollar amount that was paid.
--
if @TransAmount <> 0 and @TransAmount IS not NULL
Begin
Print 'Made it to the condition'
delete from #FINAL
WHERE
--EMPLOYEE_NAME = 'MARGARET BROWN'
TRANAMT <> @TransAmount
Print @TransAmount
Print 'After Delete'
end
If @TransAmount = 0 or @TransAmount IS NULL
Begin
Print 'The trans amount is not populated.'
End
END
DROP TABLE #FINAL
END
GOWhen I run this code, I get a really freaky result set with plenty of rows that are supposed to have been deleted still existing.
I use this to run:
Code:
USE [Reporting]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[uspCashierPaymentBalancing5_test]
@STARTDATE = N'04/16/2007',
@ENDDATE = N'04/16/2007',
@TransAmount = 40
SELECT 'Return Value' = @return_value
GOThe messages window shows this...
Code:
Made it to the condition
40.00
After Delete
(1 row(s) affected)This tells me that the code enters into my condition, that the value is 40 and that the next condition isn't being run. HOWEVER, there are plenty of rows with data that should have called for the row to be deleted based on the value and parameter in the TRANAMT field!!!
Any ideas would be appreciated!!!
Thanks,
Keith
