December 14, 2015 at 3:19 am
Hi everyone
I have a stored procedure that deletes all information out of the database by providing the customerID (which is int)
I would like to wipe out > 50000 customer datasets using this SP but I don´t want to repeat this 50k times. Can I do a "select" statement before and use the results as a variable?
Thanks in advance
December 14, 2015 at 3:33 am
This is possible. Always try to query your data as a set. For this approach it is neccessary that you give the correct WHERE clause. The delete statement can be:
DELETE custumer
WHERE cust_id > 300
It is important that you have a good knowledge about the database-structure. It is not possible to delete records when there are foreign constraints. If so then you have first to delete the foreign records. So first delete the address and than the customers. For example:
DELETE address
FROM customer
WHERE addr_customerid = cust_id
and cust_id > 300
and than:
DELETE custumer
WHERE cust_id > 300
December 14, 2015 at 3:43 am
No, you can't do this without rewriting your stored procedure. Please post table DDL in the form of a CREATE TABLE statement, sample data in the form of INSERT statements, and your stored procedure definition and we'll try to suggest something for you.
John
December 14, 2015 at 3:58 am
Below is one way of doing
Fetch all the Execution statements based on table values into a variable and using dynamic SQL execute it
DECLARE @DELETECUSTID VARCHAR(MAX)
SET @DELETECUSTID =
(
SELECT 'EXEC usp_name @CustID = ' + CAST (CustomerID as varchar(10)) + ';'
FROM YourTable
FOR XML PATH (''), TYPE
).VALUE('(./text())[1]', 'varchar(max)')
EXEC (@DELETECUSTID);
December 14, 2015 at 4:26 am
Thanks for the answer.
To be more precice
the stored procedure grabs the "customerID" provided and runs all necessary steps to delete the customer out of the database.
The command I use would be for a single user
exec deletethecustomer 23 which as expected would delete the customer with the ID 23
What I want to achieve is delete all customers > 235 up to 62340.
My select statement for all customers is
select customerid
from customertable
where valuex is not null
This query returns 60299 rows - so - how can I design my exec command as seen above to insert all 60299 rows as a variable?
This is where I am confused
December 14, 2015 at 4:43 am
Have you tried my solution?
Isn't if suffice your requirement?
December 14, 2015 at 4:53 am
Generating the EXEC statements and then running them one by one will work, but it's not the most efficient way of doing it, especially when you have so many customers to delete. If this is something you do regularly, I recommend rewriting the stored procedure so that it deletes in set-based fashion, rather than one row at a time. It sounds as if your knowledge might not be sufficient to do this, so maybe you should get someone in to help you.
John
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply