CODEWITHSUNDEEP
databasestored-procedures
Tracy Lauren
Tracy Lauren

Reputation:

can I write a procedure to delete from multiple tables?

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;

Upvotes: 1

Views: 1025

Answers (7)

Chris Nava
Chris Nava

Reputation: 6802

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

Upvotes: 1

BIBD
BIBD

Reputation: 15384

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.

Upvotes: 1

Tanveer Badar
Tanveer Badar

Reputation: 5524

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

Upvotes: 1

Joe
Joe

Reputation: 42597

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.

Upvotes: 0

SQLMenace
SQLMenace

Reputation: 134941

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

Upvotes: 4

Cade Roux
Cade Roux

Reputation: 89661

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.

Upvotes: 1

casperOne
casperOne

Reputation: 74530

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

Upvotes: 4

Related Questions