can I write a procedure to delete from multiple tables?

Question

all the procedure help I find is used for select purposes only.

can I write a table to truncate several tables?

similar to (but this does NOT work)

CREATE PROCEDURE clearall()
  BEGIN
    truncate tallgrrl.auth;
    truncate tallgrrl.factory;
    truncate tallgrrl.farm;
    truncate tallgrrl.player;
    truncate tallgrrl.timer;
  END;

Answer 1

Absolutely. One of the purposes of stored procedures is to encapsulate logic/multiple operations.

Answer 2

I don't know why your TRUNCATE isn't working. I have stored procs that TRUNCATE tables. What DB platform are you using?

Does your TRUNCATE work outside of the SP? As SQLMenace mentions, you cannot use TRUNCATE on tables with FK dependencies.

Answer 3

truncate might not work if you have relationships with that table in that case you need to either

use delete

drop the relationship truncate the table and recreate the relationship again

Answer 4

What error(s) do you get?

Besides the aforementioned FK potential issue, depending on the rights of the user executing the proc you might not have permissions to truncate.

Answer 5

Try switching to "delete from [table name]", because truncate might not work due to rights issue.

Answer 6

You should be able to. Perhaps you are doing your truncations in the wrong order (and violating the integrity constraints. e.g., you can't delete a parent until there are no children hanging off of it.

Answer 7

You may need to change the order of the truncations so that foreign keys are not truncated before the data that references them.