Loop in SP

Last Post 05 Nov 2007 09:19 AM by roma. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
roma
New Member
New Member

--
02 Nov 2007 10:35 AM
Hi ,
help me please to catch the LOOP idea in SP
I have :
Contract_XRef table (ContractXRef, contractID, FName, LName….),
Cheques_XRef (ContractXRef, ChequesID)
Cheque (ChequesID, ChequesSum,….)

I need delete some records.

Step 1
select b.ContractXRef, b.ChequesID
from Contract_XRef a, Cheques_XRef b
where a.ContractXRefID=b.ContractXRefID
and a.ContractID in (422,3626,728,703)

2. Now I need to delete from Cheques_XRef and Cheque for b.ContractXRef, b.ChequesID from step 1.

How can I pass b.ContractXRef, b.ChequesID fro this process?

Thank you
roma
New Member
New Member

--
02 Nov 2007 12:57 PM
Hi ,
i created delete SP but i got the error in the 'line 1'.
How can i delete from 2 tables ?

Delete b.*, c.* from Cheques_XRef b, Cheques c
where c.ChequesID= b.ChequesID
and b.ContractXRefID in (
select ContractXRefID
from Contract_XRef
where ContractID in (422,3626,3728
)
roma
New Member
New Member

--
05 Nov 2007 09:19 AM
Hi ,

If I run ‘DELETE b’ first and than ‘Delete c’, there is no records was found for ‘Delete C’.
If I run ‘delete C’ first , than application generated error.

DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_ Cheques_elect_ Cheques_XRef'. The conflict occurred in database, table Cheques_XRef', column 'ChequesID'.
The statement has been terminated.
SQLUSA
New Member
New Member

--
08 Nov 2007 08:43 AM
You should delete first from the foreign keyed table(s).

Second from the primary key table.

Kalman Toth, Database Architect, FALL SQL 2007 Conference in Las Vegas

SQL Server 2005 Training - http://www.sqlusa.com/order2005grandslam/

You are not authorized to post a reply.

Acceptable Use Policy