Post reply

Help with delete procedure

  • November 14, 2007 at 9:25 am

    #153961

    Hi,

    I have a delete procedure that has to check a dependent table (t2) for records before deleting from the main table (t1). If there is records the procedure has to pass a value back syaing that there is a record.

    The common column between t1 and t2 is say iCode

    How can code this in my procedure?

  • November 14, 2007 at 10:04 am

    #751163

    Rama (11/14/2007)

    Hi,

    I have a delete procedure that has to check a dependent table (t2) for records before deleting from the main table (t1). If there is records the procedure has to pass a value back syaing that there is a record.

    The common column between t1 and t2 is say iCode

    How can code this in my procedure?

    to simply not delete the values, use a delete statement that prevents it.

    create procedure sample (@val int)

    AS

    begin

    delete from t1 where iCode = @val and @val not in(select icode from t2)

    end

    --to actually raise an error:

    create procedure sample (@val int)

    AS

    begin

    declare @DBNAME varchar(30)

    set @DBNAME =db_name()

    if exists (select icode from t2 where icode=@val)

    RAISERROR

    (N'The t2 table has dependant information for value: %d, and cannot be deleted from database %s.',

    16, -- Severity.

    1, -- State.

    @val , -- First substitution argument.

    @DBNAME); -- Second substitution argument.

    delete from t1 where iCode = @val and @val not in(select icode from t2)

    end

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 14, 2007 at 1:28 pm

    #751245

    🙂

    Thanks !!

  • November 14, 2007 at 1:46 pm

    #751254

    Isn't that just reengineering a foreign key constraint? Wouldn't the built-in version do just as well?

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply