Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2009
    Posts
    11

    Unanswered: Stored Procedures -Mutiple customerids and Output File

    I am new to SQL I wrote the following stored procedure to delete several records from different tables based on customerid in each table.


    CREATE PROCEDURE dbo.deletecustomerKH
    @cid INT AS
    Begin

    DELETE trImageText
    FROM trImageText JOIN trItem ON trImageText.TableID = trItem.trItemID
    WHERE trItem.trCustomerID = @cid

    DELETE
    FROM trItem
    WHERE trItem.trCustomerID = @cid


    DELETE
    FROM trEmployee
    WHERE trEmployee.trcustomerid = @cid


    DELETE
    FROM trDepartment
    WHERE trDepartment.trcustomerid = @cid

    DELETE
    FROM trServices
    WHERE trServices.trcustomerid = @cid


    DELETE
    FROM trCustomer
    WHERE trCustomer.trcustomerid = @cid

    End


    I know that I can execute a delete on the procedure below:


    EXEC dbo.deletecustomerKH @cid = '236'


    My Question is

    1) I have about 700 customerid's in a excel file which I could import into a table called deletecid and a field called cusid for example.

    How do I run the stored procedure so it steps through each of the 700 customerid's and runs the procedure above and delete's the records from each of the statements until it has deleted all the customerid's in my list.

    2) How can I output the results to a text file with the number of rows deleted in each of statements for each customerid.

    Any help will be appreciated - KH

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You know, if you created foreign key relationships (which based on the barebone of information posted above sounds appropriate), with the on delete cascade option, you wouldn't have to worry about writing 5 delete statements.

    You don't have to step through the list if you don't want either, you can join to your imported table (like you've done in your first delete) and it'll be done as a single operation.

    As for number of rows affected - if you're on 2005 or 2008 you have the OUTPUT clause which which you can use.
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2009
    Posts
    11
    Thanks for the response georgev-

    Sorry I did not post the SQL server is 2000: Also I did not create this database it was created from a third party and is in production - the client is not happy with the service so they asked me to help. I don't believe there was Foreign keys created.

    How do I the join on the first delete statement because trImageText table does not have a customerid field only the trText table has the customerid field. that is why I did a join between the two tables.


    Also what is the syntax to write for the output file in SQL 2000

    KH

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Rewrite
    Code:
    DELETE trImageText
    FROM trImageText JOIN trItem ON 
    trImageText.TableID = trItem.trItemID
    WHERE trItem.trCustomerID = @cid
    as
    Code:
    DELETE 
    FROM trImageText 
    WHERE EXISTS	(SELECT 1 
    		FROM trItem 
    		WHERE trItem.trCustomerID = @cid AND 
    			trImageText.TableID = trItem.trItemID
    		)
    Last edited by Wim; 02-04-09 at 07:03.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I'm a DB2 guy, just starting with SQL Server. Can
    Code:
    DELETE trImageText
    FROM trImageText JOIN trItem 
    ON trImageText.TableID = trItem.trItemID
    WHERE trItem.trCustomerID = @cid
    run in SQL Server?


    Instead of
    Code:
    DELETE 
    FROM trImageText 
    WHERE EXISTS	(SELECT 1 
    		FROM trItem 
    		WHERE trItem.trCustomerID = @cid AND 
    			trImageText.TableID = trItem.trItemID
    		)
    would
    Code:
    DELETE 
    FROM trImageText 
    WHERE TableID IN (SELECT trItem.trItemID 
    		FROM trItem 
    		WHERE trItem.trCustomerID = @cid
    		)
    be faster?
    Last edited by Wim; 02-04-09 at 07:09.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Feb 2009
    Posts
    11
    Thank You all for your reply's .

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    EXISTS is usually faster though for such a simple expression they probably evaluate to the same plan.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •