delete help

[rjhe22]

i have this stored procedure

USE [Harvest] 
GO 
/****** Object: StoredProcedure [dbo].[uspProcessDocumentDeleteQueue] Script Date: 10/22/2008 10:11:37 ******/ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 

ALTER    PROCEDURE [dbo].[uspProcessDocumentDeleteQueue] 
AS 

--Set up a counter so we know when all are processed 
DECLARE @count int 
SET @count = (SELECT COUNT(*) 
                FROM    dbo.DocumentDeleteQueue ddq 
                WHERE    ddq.Deleted = 0) 


--Main loop - runs untill all are processed 

WHILE @count > 0 
BEGIN -- mail loop 


--Set the DocumentDeleteID as next one in line 
DECLARE @dID uniqueidentifier 
SET @dID = (SELECT TOP 1 ddq.DocumentDeleteID 
                FROM    dbo.DocumentDeleteQueue ddq 
                WHERE    ddq.Deleted = 0 
                ORDER BY ddq.DeleteDate) 

--Is it a Receipt or DA/TI 
DECLARE @doc nvarchar(256) 
SET @doc = (SELECT TOP 1 dt.DocumentType 
                FROM    dbo.DocumentDeleteQueue ddq 
                INNER 
                JOIN    dbo.DocumentType dt 
                ON        dt.DocumentTypeID = ddq.DocumentTypeID 
                WHERE    ddq.DocumentDeleteID = @dID) 

--Get the Document ID, could be either 
DECLARE @docID bigint -- Get a genericID, could be either 
SET @docID = ( SELECT TOP 1    DocumentID 
                FROM    dbo.DocumentDeleteQueue ddq 
                WHERE    ddq.DocumentDeleteID = @dID) 


--If it is a receipt 
IF @doc = 'Receipt' 
BEGIN -- Start receipt 

--Perform the Receipt specific process0 
--For Test 
SELECT 'Do Receipt' 


--Find the EntryID for the Receipt and put it in a variable 
--DECLARE @eID uniqueidentifier 
--SET @eID = (SELECT TOP 1 EntryID FROM dbo.Receipt -- top 1 because must select only 1 
--                WHERE ReceiptID = @docID) 
-- 
----Put the EntryTask Info for the Entry into the DocumentTask Table 
--INSERT INTO dbo.DocumentDeleteTask 
--(    DocumentDeleteID, 
--    TaskID    , 
--    UserID    ) 
--SELECT    @dID        , 
--        et.TaskID    , 
--        et.UserID 
--FROM    dbo.EntryTask et 
--WHERE    et.EntryID = @eID 
-- 
----Add Current User as the Deleter (not sure how to, may be a parm coming in) 
----Need to do this before actual deletes start 
----NOTE could be a proc itself as it will be common to both types 
-- 
-- 
----Call Entry Purge to do the actual delete 
-- 
--EXEC Harvest.dbo.uspAccountPurge @eID 


END -- End receipt 
ELSE 
--BEGIN 
--SELECT 'Do any other' 

--Find the EntryID for the DA/TI, Join to Journal, as DA table does not have it 
DECLARE @eID uniqueidentifier 
SET @eID = (SELECT TOP 1 daj.EntryID 
                FROM    dbo.DebitAuthorityJournal daj -- top 1 because must select only 1 
                INNER 
                JOIN    dbo.DebitAuthority da 
                ON        da.DebitAuthorityID    =    daj.DebitAuthorityID     
                WHERE    da.DebitAuthorityID    =    @docID) 

--If Null suggests to look at Company 
IF @eID IS NULL 
BEGIN 
--Same as above but looks at Company versions 
SET @eID = (SELECT TOP 1 dacj.EntryID 
                FROM    dbo.DebitAuthorityCompanyJournal dacj -- top 1 because must select only 1 
                INNER 
                JOIN    dbo.DebitAuthority da 
                ON        da.DebitAuthorityID    =    dacj.DebitAuthorityID     
                WHERE    da.DebitAuthorityID    =    @docID) 



--If all is well should have the entry ID in eID 
--So now what? 
INSERT INTO dbo.DocumentDeleteTask 
(    DocumentDeleteID, 
    TaskID    , 
    UserID    ) 
SELECT    @dID        , 
        et.TaskID    , 
        et.UserID 
FROM    dbo.EntryTask et 
WHERE    et.EntryID = @eID 

----Question - What if the Number was wrong? - 9999999999 
---- leave for later but whateverwe do here should also happen for receipts 

EXEC Harvest.dbo.uspAccountPurge @eID 


UPDATE    dbo.DocumentDeleteQueue 
SET        Deleted = 1    , 
        DeleteDate = GETDATE()     
WHERE    DocumentDeleteID = @dID 

----Reset the count to make sure the loop finishes when done 
SET @count = (SELECT COUNT(*) 
                FROM    dbo.DocumentDeleteQueue ddq 
                WHERE    ddq.Deleted = 0) 


END -- Main loop for updating the document delete table 
END 

-- 
--UPDATE dbo.DocumentDeleteQueue 
--SET Deleted = 0

when uspaccount purge run its suppose to delete stuff fronm tables

but i cant get it to work. i first need to get entryid from one table and then get the debitauthorityid from that and match it to the debitauhorityid in the other table
anyideas. here is what i have tried

DECLARE @jnl TABLE 
(    EntryID    uniqueidentifier    ) 
INSERT INTO @jnl ( EntryID ) 
SELECT EntryID FROM dbo.DebitAuthorityJournal 
WHERE DebitAuthorityID = @debitAuthorityID 

INSERT INTO @jnl ( EntryID ) 
SELECT EntryID FROM dbo.DebitAuthorityCompanyJournal 
WHERE DebitAuthorityID = @debitAuthorityID 
--SELECT * FROM @jnl 




--Delete Debit Authority First 
DELETE    dbo.DebitAuthorityJournal 
WHERE    DebitAuthorityID = @debitAuthorityID 

DELETE    dbo.DebitAuthorityCompanyJournal 
WHERE    DebitAuthorityID = @debitAuthorityID 

DELETE    dbo.DebitAuthority 
WHERE    DebitAuthorityID = @debitAuthorityID

 

[rjhe22]

anyone able to help as i am very stuck