Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Trouble deleting Rows from Temp Table in SP

Status
Not open for further replies.

kxramse

Programmer
Jul 28, 2006
75
US
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...

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
GO

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:

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

GO

The 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
 
Keith -

I can't read your post. In the future you should try not to post long strings of text with no spaces (like Column,Column2,Column3) because it makes the thread get wider than the screen, and IE6 has no scroll bar.

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom
 
I see your point, but I'm its already done. Any chance you see the problem? Or is your screen just small? I have can see 90% of it.
 
Nothing immediately comes to mind. You might want to make this change:

Code:
[COLOR=blue]if[/color] [COLOR=#FF00FF]isnull[/color](@TransAmount, 0) <> 0 
    [COLOR=blue]Begin[/color]
        [COLOR=blue]Print[/color] [COLOR=red]'Made it to the condition'[/color]
        [COLOR=blue]delete[/color] [COLOR=blue]from[/color] #FINAL
                [COLOR=blue]WHERE[/color] 
                    [COLOR=green]--EMPLOYEE_NAME = 'MARGARET BROWN'
[/color]                    TRANAMT <> @TransAmount 
        [COLOR=blue]Print[/color] @TransAmount
        [COLOR=blue]Print[/color] [COLOR=red]'After Delete'[/color]
    [COLOR=blue]end[/color]
[COLOR=blue]Else[/color]
        [COLOR=blue]Begin[/color]
            [COLOR=blue]Print[/color] [COLOR=red]'The trans amount is not populated.'[/color]
        [COLOR=blue]End[/color]

Another question is, why are you deleteing the rows from the temp table if you're not going to do anything with it. Why not just print @tran amount then drop the temp table?

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top