Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-01-21 : 08:46:32
|
| SasiKumar writes "I would like to delete large data in SQL2000 DB.1. I am storing some data into 20 different tables in the same data base. I want to delete the data based on some filter. There is a main table which has all the fileds. The only common filed in all other tables is "CreationDate". From the main table I should get the "CreationDate" based on the filter passed. I need to pass this "CreationDate" one by one to other tables to delete the data.I would like to write a single stored procedure for the above scenario. Please help me." |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-01-21 : 10:26:41
|
| Assuming you have referential integrity in your database, you can't just loop through all tables and delete rows based on a "CreateDate" ....- Jeff |
 |
|
|
epanther
Starting Member
8 Posts |
Posted - 2005-01-21 : 14:11:24
|
| The databases I support are not on 2000 yet and I have to delete related items from all of my tables. Because of referential integrity, I have to do them in a certain order. I wrote a sproc that goes through them in the correct order. It is a pretty simple sproc. I pretty much send a variable in, the sitecode in my case, and then "delete from table where sitecode = @sitecode" over and over for all of the tables.Since you are on 2000, there is a lot easier way to deal with referential integrity. Look up CASCADE. Since I haven't been able to use it, I'm not very familiar with it yet. I will say that this is a very powerful command and you better know what you are doing and be very familiar with your database if you use it. You know how it sucks when you forget the "where" clause on a delete statement, imagine what will happen when you use the CASCADE command too.Panther |
|
|
|
|
|
|
Handling the return values from Stored Procedures